Top Hotels Per City Query | Dataford Interview Questions

Write a SQL query to find the top 3 most reviewed hotels in each city by average rating.

Use the hotels and reviews tables. Include only hotels with at least one non-null rating.

Output

One row per qualifying hotel, with city, hotel_id, hotel_name, review_count, average_rating, and city_rank.
Return at most three hotels per city, ranked by average rating descending, then review count descending, then hotel ID ascending.
Order the final results by city ascending and city rank ascending.

hotels

Column	Type	Description
hotel_idPK	INT	Unique hotel identifier
hotel_name	VARCHAR(150)	Hotel name
city	VARCHAR(100)	City where the hotel is located

reviews

Your question is Top Hotels Per City Query. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Write a SQL query to find the top 3 most reviewed hotels in each city by average rating.

Use the hotels and reviews tables. Include only hotels with at least one non-null rating.

One row per qualifying hotel, with city, hotel_id, hotel_name, review_count, average_rating, and city_rank.
Return at most three hotels per city, ranked by average rating descending, then review count descending, then hotel ID ascending.
Order the final results by city ascending and city rank ascending.

hotels

Column	Type	Description
hotel_idPK	INT	Unique hotel identifier
hotel_name	VARCHAR(150)	Hotel name
city	VARCHAR(100)	City where the hotel is located

reviews

Your question is Top Hotels Per City Query. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.