SQL PUZZLE | Refill missing data with nearest data using Single SELECT | SQL Interview Question

In this puzzle you have to replace the NULL values present in the column Vals with the next NON null value. If the next value is also NULL then replace that with the previous NON null value. All this you have to do in a single select. Please check out sample input and expected output.

Sample Input

Expected output

Script – DDL and INSERT sample data

--

CREATE TABLE GetNearestData
(
	 Id INT IDENTITY (1,1)
	,Vals INT
)
GO

INSERT INTO GetNearestData VALUES
(100),
(100),
(NULL),
(200),
(120),
(12),
(NULL),
(121),
(NULL),
(105),
(103),
(111),
(NULL)
GO

SELECT * FROM GetNearestData
GO

--

SOLUTION – 1

--

SELECT Id, Vals
	,COALESCE(Vals,LEAD(Vals) OVER (ORDER BY Id),LAG(Vals) OVER (ORDER BY Id)) NewVals
FROM GetNearestData
GO

--

OUTPUT – 1

--

Id          Vals        NewVals
----------- ----------- -----------
1           100         100
2           100         100
3           NULL        200
4           200         200
5           120         120
6           12          12
7           NULL        121
8           121         121
9           NULL        105
10          105         105
11          103         103
12          111         111
13          NULL        111

(13 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com