Using Rank() and PARTITIONs in SQL To Solve Complex SQL Interview Questions

This video will walk you through the Wine Magazine 'Most Expensive And Cheapest Wine' SQL interview question solution. If you're struggling to solve interview questions in SQL, this tutorial will help you out! By the end of this video, you'll be able to solve this question using SQL and improve your SQL skills for future interview questions!

______________________________________________________________________

______________________________________________________________________

Timeline:

Intro: (0:00​​​)
Interview Question: (0:19 )
Exploring and understanding the dataset: (0:28)
Coding the solution: (2:14​​)
Conclusion: (​7:03)

______________________________________________________________________

About The Platform:

______________________________________________________________________

Contact:

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

______________________________________________________________________
#StrataScratch #SQLInterviewQuestion #WineMagazineSQLQuestion

[ashhere]

Hi StrataScratch, why did you perform a Union ALL in the initial CTE? Iff any duplicates, don't we want to exclude them?

[petertran]

How would this work if there is two wines with the same price. Rank() would mark them both as the same ranking and u will lose this once u apply the max function?? Big edge case missing

[architectaq]

my solution:

with cte1 as
(select region_1 as region, variety, price
from winemag_p1
union all
select region_2, variety, price
from winemag_p1)

select a.region, a.cheapest_variety, b.expensive_variety from
(select distinct region, variety as cheapest_variety, price as cheapest_price
from cte1
where (region, price) in (select region, min(price) from cte1 group by region)) a
join
(select distinct region, variety as expensive_variety, price as expensive_price
from cte1
where (region, price) in (select region, max(price) from cte1 group by region)) b
using(region);

[techiewithcamera]

My Solution:
with final_view as
(select region_1 region, price, variety
from winemag_p1
where region_1 is not null
union
select region_2 region, price, variety
from winemag_p1
where region_2 is not null)
select t1.region,
t2.variety expesive_variety,
t3.variety cheapest_variety
from
(select region,
max(price) expensive,
min(price) cheapest
from final_view
group by 1)t1
join final_view t2 on t1.region=t2.region and t1.expensive=t2.price
join final_view t3 on t1.region=t3.region and t1.cheapest=t3.price
order by 1;