SQL Interview Questions And Answers Part 60 | How to fill NULL values with the last Non NULL value

SQL Interview Questions And Answers Part 60 | How to forward fill NULL values with the last Non NULL value

This question has been asked in many company and it will be helpful in cracking any SQL interview
Problem Statement : Stocks Table consists of DateKey, StockName and Price.
Write a SQL query to derive another column MarketPrice where it is going to forward fill NULL values with the last Non NULL value


-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
*Instagram :

*Twitter:
-------------------------------------------------------------------------
Table and Insert SQL Script :
-------------------------------------------------------------------------
create table Stocks
(DateKey Date,
StocksName varchar(20),
Price int
);

insert into Stocks values
('20230101','Infosys',1400)
,('20230102','Infosys',null)
,('20230103','Infosys',1450)
,('20230104','Infosys',null)
,('20230105','Infosys',null)
,('20230105','Infosys',null)
,('20230101','Reliance',2300)
,('20230102','Reliance',NULL)
,('20230103','Reliance',null)
,('20230104','Reliance',2375)
,('20230105','Reliance',2400)
,('20230106','Reliance',null)

#WALMARTInterview #FAANG #SQLInterviewQuestionsandanswers #sqlInterviewQuestions #MAANG #sqlInterviewQuestionsForTesting

[arunkumarn]

-- i believe running total instead of first_value would work too.

with cte as(
select *, count(price) over (partition by stocksname order by datekey) as rn from stocks
)
select *, sum(price) over (partition by stocksname, rn order by datekey) as MarketPrice from cte

[PiyushSharma-jqrr]

This can re-write with simple logic.
select datekey, stocksname, price,
max(price) over(partition by stocksname order by datekey) as rnk4
from Stocks;

[VARUNTEJA]

with cte as
(select *, count(price)over(partition by year(datekey) order by eid)ranks from test10)
select datekey, stockname, price, first_value(price)over(partition by ranks order by eid)marketprice from cte

[prabhatgupta]

with a as (select *, row_number()over() as k from stocks)
select datekey, stocksname, price, first_value(price)over(partition by count) from(select *, count(price)over(order by k) as count from a)X

USING FIRST VALUE FUNCTION

[praveenagokul]

Hello Please reply, if we can use the "ignore nulls" in the lag windows function we can easily solve this. I Have tried many a times throwing error. It has to work, I don't no what's the mistake. Can you please try and let me know.

[grzegorzko]

SELECT
datekey
, stocksname
, price
, MAX(price) OVER(PARTITION BY stocksname ORDER BY stocksname, datekey) AS MarketPrice
FROM Stocks
ORDER BY stocksname, datekey;