Day 10: Solving Zomato SQL Interview Questions | 100 Days Challenge | Group By | PostgreSQL

Welcome to Day 10 of my 100 Days Challenge! Today, we're diving into UBER SQL interview questions to help you ace your next tech interview.

Join me as I walk through each problem step-by-step, providing detailed explanations and practical tips. Don't forget to like, subscribe, and hit the bell icon to stay updated with daily challenges!

Join the 100 Days Challenge Community:

Get the question & datasets

Relevant Playlists:

#AmazonInterview #SQLInterviewQuestions #100DaysChallenge #DataScience #SQLTutorial #TechInterviews #DataAnalysis #LearnSQL #InterviewPrep #CodingChallenge #TechCareer #SQLPractice #AmazonTech #DataEngineering #PythonInterview #ExcelTips"

[HarishParamathmananda]

Thanks for the another insightful problem-solving video,
I have 2 doubts,
1) By changing the order will it affect the result?
You connected cities -> restuarant -> orders,
If we connect
select
*
from
orders o
join restaurants r on r.restaurant_id = o.restaurant_id
join cities c on c.city_id = r.restaurant_id
2) ROW_NUMBER() instead of dense_rank -> if we use row_number and select the rid >= 2(top 2) will this be right?

[Krishnaisreal]

Brother you can use HAVING here instead of subquery.

[pratikjugantmohapatra]

code not uploaded in github yet
please check

[scandium]

why did we not use row_number instead of dense_rank, was it beacuse dense_rank deals with duplicates?

[abhishekdubey]

will the same code will work in my sql?

[Savenature]

with cte as (select c.city_id, city_name, r.restaurant_id, r.restaurant_name, rating,
round((sum(rating) over (partition by city_name, restaurant_name)) /
(count(1) over (partition by city_name, restaurant_name)), 2) as avg_rating
from cities c
inner join
restaurants r
on c.city_id=r.city_id
join rest_orders ro
on r.restaurant_id=ro.restaurant_id), cte2 as (
select *, dense_rank() over(partition by city_name order by avg_rating desc) as rnk
from cte)
select distinct city_id, city_name, restaurant_name, avg_rating, rnk
from cte2
where rnk<=2;

[saipranay]

WITH AvgRating AS (
SELECT r.restaurant_id, r.restaurant_name, c.city_name, AVG(o.rating) AS avg_rating
FROM restaurants r
JOIN orderss o ON r.restaurant_id = o.restaurant_id
JOIN cities c ON r.city_id = c.city_id
GROUP BY r.restaurant_id, r.restaurant_name, c.city_name
),
ranks AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY city_name ORDER BY avg_rating DESC) AS rn
FROM AvgRating
)
SELECT city_name, restaurant_name, avg_rating
FROM ranks
WHERE rn <= 2
ORDER BY city_name, rn;