"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.
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.
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.
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?
Since the count is based on the partitioning in currency key, the second grouping would also need the currency key partitioning. The count values for different currency keys can repeat.
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.
@@LearnatKnowstar I should thank you infact. Your videos are really really helpful, especially for cracking SQL interviews. I got a lot more benefited. 🙏
How to implement this logic for columns ,below is an example for same.. Brand 202101 202102 202103. 202104 A. Null. 20. Null. Null Expected output Brand 202101 202102 202103. 202104 A. Null. 20. 20. 20.
"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.
Thank you for sharing your thoughts. It is a great way for us to learn and grow. Really appreciate your perspective and it is well noted 👍
Nice explanation.! Very Informative..Thank you..Keep making these kind of videos it really helps us a lot
Glad it was helpful 🙏
I am aware of one solution using CTE but this is an excellent solution- neat and tidy !
Thank you 🙏
Very very important concept and in easy to understand way of explaining.
Glad it was helpful!
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.
We have already covered this scenario in one of our tutorials. You can check it out here - th-cam.com/video/o4f43RdkiWo/w-d-xo.html
Hope it helps!
@@LearnatKnowstar Thanks, it totally worked.
Very informative not sure why people will not recommend this type of channel
Thank you for your support !
Great explanation. Waiting for more videos.
Thank you. More videos coming soon
Thanks for the video, what if i want to take this for the last not null and apply to evert column of a table?
Nice explanation 👌 👍 👏
Thank you 🙏
Thank you for such an excellent question and solution! Keep up with hard work!
Glad it was helpful 🙏
extremely helpful, great video!!
Glad it was helpful!
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.
Very helpful
Thank you
Nice explanation mam.. Good job keep going ❤
Glad it was helpful.
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?
Thank you! How the First_value() function picking 0.9108? which is the last value in the group since our order by is ascending.
Great👍👏
Thanks 😊
Thank you so much for bringing this topic ❤️
Glad it was helpful
Which Database/table are you using for this example? im in AdventureWorks 2019
Please make video fifo method mutual fumd calculation in SQL
Sure. We will plan more videos based on these business scenarios soon 👍
Why do you have partition by Currenykey 2nd time. could we have used partition by Grp only instead of both?
Since the count is based on the partitioning in currency key, the second grouping would also need the currency key partitioning. The count values for different currency keys can repeat.
Can you provide the DDLs and insert statements in comments ? It will be very useful for us to practice
They are provided in the link in the description. Hope this helps!
Thanks
how can we solve with Case Statement please
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.
Thank you for sharing your doubts and thoughts. It really is helpful for all 👍
@@LearnatKnowstar I should thank you infact. Your videos are really really helpful, especially for cracking SQL interviews. I got a lot more benefited. 🙏
How to implement this logic for columns ,below is an example for same..
Brand 202101 202102 202103. 202104
A. Null. 20. Null. Null
Expected output
Brand 202101 202102 202103. 202104
A. Null. 20. 20. 20.
You might need to unpivot the data first.
Below tutorial on Unpivot might be helpful for you.
th-cam.com/video/mf4ABpHqgag/w-d-xo.html