"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.

@LearnatKnowstar

Жыл бұрын

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 👍

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.

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

@LearnatKnowstar

Жыл бұрын

Glad it was helpful 🙏

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

@LearnatKnowstar

Ай бұрын

Glad it was helpful!

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

@LearnatKnowstar

Жыл бұрын

Glad it was helpful 🙏

Great explanation. Waiting for more videos.

@LearnatKnowstar

Жыл бұрын

Thank you. More videos coming soon

extremely helpful, great video!!

@LearnatKnowstar

4 ай бұрын

Glad it was helpful!

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

@LearnatKnowstar

Жыл бұрын

Thank you 🙏

Thank you so much for bringing this topic ❤️

@LearnatKnowstar

Жыл бұрын

Glad it was helpful

Very helpful

@LearnatKnowstar

Жыл бұрын

Thank you

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 👌 👍 👏

@LearnatKnowstar

Жыл бұрын

Thank you 🙏

Nice explanation mam.. Good job keep going ❤

@LearnatKnowstar

5 ай бұрын

Glad it was helpful.

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

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?

Great👍👏

@LearnatKnowstar

Жыл бұрын

Thanks 😊

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

@LearnatKnowstar

Жыл бұрын

Thank you for your support !

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.

@LearnatKnowstar

Жыл бұрын

We have already covered this scenario in one of our tutorials. You can check it out here - kzread.info/dash/bejne/oWialpWLlM7Ph9I.html Hope it helps!

@riddlex

Жыл бұрын

@@LearnatKnowstar Thanks, it totally worked.

Please make video fifo method mutual fumd calculation in SQL

@LearnatKnowstar

Жыл бұрын

Sure. We will plan more videos based on these business scenarios soon 👍

how can we solve with Case Statement please

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

@LearnatKnowstar

Жыл бұрын

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

@LearnatKnowstar

Жыл бұрын

They are provided in the link in the description. Hope this helps!

@sarvesht7299

Жыл бұрын

Thanks

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

Жыл бұрын

Thank you for sharing your doubts and thoughts. It really is helpful for all 👍

@karthikvenkataram4790

Жыл бұрын

@@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.

@LearnatKnowstar

Жыл бұрын

You might need to unpivot the data first. Below tutorial on Unpivot might be helpful for you. kzread.info/dash/bejne/n5poo6SpeNTNkco.html