How to use SQL window functions: RANK vs DENSE_RANK in Data Science Interviews

This video covers two of the most common SQL Rank Functions, their differences, and how to use them. We’ll take the following two example questions from data science interviews which shows the crucial reasons to use RANK and DENSE_RANK SQL window functions.

Link to the questions:

______________________________________________________________________

______________________________________________________________________

Timeline:

Intro: (0:00​​​)
Interview Question #1: (0:25)
Interview Question #2: (6:24)
Conclusion: (9:02​​)
______________________________________________________________________

About The Platform:

______________________________________________________________________

Contact:

If you have any questions, comments, or feedback, please leave them here!
______________________________________________________________________

[sharathgowdayr]

Great thought process while solving SQL interview questions.

[gamergoosling]

super helpful. i’d greatly appreciate more videos that cover the use cases of any and all SQL functions that are commonly used in industry. thanks!

[yuthpatirathi]

Thank you for the second I used this solution

with tt as (select sum(n_messages) as s, id_guest from airbnb_contacts
group by 2
order by 1 desc)
select dense_rank() over ( order by s desc), id_guest, s from tt ;

[florincopaci]

Thank you for all the videos from which i learned i lot.Thank you!
It is possible to do in the future a video about the question -2153. The Number of Passengers in Each Bus II from LeetCode? please.

[techiewithcamera]

My Solution:
select city,
count(distinct business_id) busi_cnt,
dense_rank() over (order by count(distinct business_id) desc) rank
from yelp_business
where stars=5
group by 1
order by 2 desc;

[techiewithcamera]

My Solution:
select id_guest,
sum(n_messages),
dense_rank() over (order by sum(n_messages) desc)
from airbnb_contacts
group by 1
order by 2 desc;