Top Hotels Per City Query | Dataford Interview Questions

Write a query to find the top 3 most booked hotels in each city for the last 30 days.

Count booking records whose booking date falls within the 30-day period ending today. Include only hotels represented in the hotel table. If a city has fewer than three qualifying hotels, return all available hotels.

Output

One row per qualifying hotel, with city, hotel_id, hotel_name, booking_count, and hotel_rank.
Include the top three hotels per city, ordered by city, rank, and hotel ID. Break booking-count ties by the lower hotel_id.

Schema

hotels

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

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
hotel_id	INT	Hotel associated with the booking
booked_at	DATE	Date on which the booking was made

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.

Problem