SQL Problem Solving: Replace NULLs with Previous Row Values | Window Functions Tutorial

Join us in this SQL problem-solving session as we tackle the challenge of replacing NULL values with the corresponding values from the previous row. In this tutorial, we'll leverage the power of SQL's FIRST_VALUE and COUNT window functions to achieve a seamless solution. Follow along step-by-step to master these window functions.

📋 Problem Description:
The "mom_2004" table contains information about cricket matches, including match numbers, players of the match, and opponents. We'll focus on solving the problem of replacing NULL values in the 'mom_player' column with the values from the previous row. This involves using the FIRST_VALUE and COUNT window functions strategically.

👉 Don't forget to like, share, and subscribe for more SQL problem-solving videos and tutorials! Stay tuned for additional challenges in our SQL playlist.

Table creation & insertion script:
CREATE TABLE mom_2004 (
match_no int,
mom_player varchar(50),
match_opponent varchar(50)
);

INSERT INTO mom_2004 VALUES
(1, 'Virender Sehwag', 'South Africa'),
(2, 'Sourav Ganguly', 'South Africa'),
(3, NULL, 'South Africa'),
(4, 'Sachin Tendulkar', 'England'),
(5, 'Sourav Ganguly', 'England'),
(6, 'Sachin Tendulkar', 'England'),
(7, NULL, 'England'),
(8, NULL, 'Bangladesh'),
(9, 'Rahul Dravid', 'Australia'),
(10, NULL, 'Australia');

#sql #interview #windowfunctions #firstvalue #count

[narasimhareddykonireddy]

Nice problem and explanation as well 😍

[ishwarkokkili]

this also works :
SELECT
match_no,
COALESCE(
mom_player,
(SELECT top 1 mom_player FROM mom_2004 AS prev WHERE prev.match_no < m.match_no AND mom_player IS NOT NULL ORDER BY prev.match_no DESC)
) AS mom_player,
match_opponent
FROM
mom_2004 AS m
ORDER BY
match_no;

[yashmishra]

Hello sir I have used LAG function to solve this problem, but I feel that my solution is hard coded. I tried to use recursive CTE by giving a condition IS NULL and populate until it's not NULL. Can you please solve it using LAG and Recursive CTE if possible??
with cte as
(select *, LAG(mom_player) over (order by match_no) new_player
from mom_2004),
cte2 as
(select match_no, (case when mom_player IS Null then new_player else mom_player end) as mom_player,
match_opponent
from cte),
cte3 as
(select *, LAG(mom_player, 1, mom_player) over (order by match_no) new_player
from cte2)

select match_no, (case when mom_player IS Null then new_player else mom_player end) as mom_player,
match_opponent
from cte3