Must DO - SQL Interview Query | Forward Fill Values for NULL Records | Last Not NULL puzzle | FAANG

In this tutorial, we write a SQL Query to forward fill values for NULL records. This is also known as the Last Not NULL puzzle.

We make use of Windows function FIRST_VALUE. We also write the query using MAX function.

This is a FAANG interview query.

The practice data and SQL Queries are available here-

Difference between Count(*), Count(1) and Count(column)

How to install SQL Server for practice?

Check out the complete list of SQL Query Interview Questions -

Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization

Data Science Fundamentals with Python and SQL Specialization

Beginners to Python Programming

Data Science and Business Analytics with Python

Get 40% OFF of Skillshare with code FLASH40 - October 2022

Python for Everybody Specialization

Google Data Analytics Professional Certificate

Coursera Plus - Data Science Career Skills

Please do not forget to like, subscribe and share.

For enrolling and enquiries, please contact us at

[jamesgg]

"Count(Rate) OVER..." relies on the aggregate function treating NULL as invalid. Even though it will work in nearly 100% of cases, it's like abbreviating "cumulative group count" as "cum grp cnt", it says something about you that only experts will notice... and not in a good way.

Instead of "Count(Rate) OVER..." I would use "SUM( CASE WHEN Rate IS NULL THEN 0 ELSE 1 END ) OVER..." simply because it explains what we're doing with NULL. I would also include a comment briefly explaining why we're using NULL like that and (more importantly) who made that design decision.

[fahadmahmood]

Nice explanation.! Very Informative..Thank you..Keep making these kind of videos it really helps us a lot

[kristyowens]

I am aware of one solution using CTE but this is an excellent solution- neat and tidy !

[WrongTurn-czlu]

Very very important concept and in easy to understand way of explaining.

[karthikvenkataram]

Excellent question and excellent solution. Thanks a lot "Learn at Knowstar" for getting these kinds of questions to our knowledge.
This channel for SQL is really helping me out for interviews.

One doubt is that, should we not use RANK() over (partition by CurrencyKey) in our CTE table clause?
Will that still not work?

I dont have the editor to try it myself, if someone can try pls let me know.

I am trying to install an editor to see it myself, and update here, in case if there is someone who can share some insights to it before then, please do so.

Thank youj.

[darrylw]

Which Database/table are you using for this example? im in AdventureWorks 2019

[srinivasbusi]

Very informative not sure why people will not recommend this type of channel

[pradeepmohan]

Nice explanation mam.. Good job keep going ❤

[djjiang]

Thank you for such an excellent question and solution! Keep up with hard work!

[riddlex]

This is helpful. I was asked an interview question where I had to pair matches between two teams from a set of 10 teams. The pairing should be in a way that the same team shouldn't be paired. For e.g. Team A cannot be paired with team A, and if Team A has been paired with team B, then team B shouldn't be paired with Team A again. To approach this, I used cross join on the same table, and I removed the teams that were the same. However, I was not able to figure out how to keep only A-B paring. My output had both A-B & B-A pairing.

[hv]

Great explanation. Waiting for more videos.

[mocanada]

Thank you for the video!
It doesn't work if the value if non-numeric.
Let's say we have product name which has value only for the first row and has null for the rest until we have another product and the rate for the product.
Can you make one video based on the non-numeric values?

[MrChilo]

Thanks for the video, what if i want to take this for the last not null and apply to evert column of a table?

[peak]

Thank you so much for bringing this topic ❤️

[kingstonxavier]

Thank you! How the First_value() function picking 0.9108? which is the last value in the group since our order by is ascending.

[vijayvishwakarma]

Please make video fifo method mutual fumd calculation in SQL

[vinayak]

Why do you have partition by Currenykey 2nd time. could we have used partition by Grp only instead of both?

[Gyan_Hub_]

how can we solve with Case Statement please

[sarvesht]

Can you provide the DDLs and insert statements in comments ? It will be very useful for us to practice

[karthikvenkataram]

Hi All, based on my below comment the RANK() or DENSE_RANK does NOT work here because, the RANK() increments values against NULL values in the column.

Unfortunately, am unable to share the screenshot here, but I believe you all understand what I mean.

Hope this helps.

Thank you.