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

[@chandanpatra1053]

very good explanation.

[@ItJunction4all]

Thank you 😍

[@alano444]

Very well explained

[@ItJunction4all]

Thank you so much !

[@arunkumarn8710]

-- 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-jq8rr]

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

[@billcates4048]

this wont work ,if price decreases

[@VARUNTEJA73]

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

[@praveenagokul4038]

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.

[@gerry_blandu]

Thank you Sir

[@ItJunction4all]

You are welcome!

[@prabhatgupta6415]

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

[@ItJunction4all]

Hi Prabhat ! Which database u have used to frame this query ?

[@prabhatgupta6415]

@@ItJunction4all sir i used my sql

[@grzegorzko55]

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

[@ItJunction4all]

HI Grzegorzko ! Thanks for posting your solution. Your solution will not work if the price of stocks decreases Eg. Infosys price is 1400 on 1st Jan and drops to 1300 on 3rd Jan.
You can try your code with below set of data. It will not work as expected.
insert into Stocks values
('20230101','Infosys',1400)
,('20230102','Infosys',null)
,('20230103','Infosys',1300) -- Price dropped here
,('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)