SQL Window Functions for Top Destinations

MediumSQL · PostgreSQL

Problem

Write a SQL query using window functions to find the top three most-booked hotel destinations for each customer segment over the last quarter at Expedia Group.

For this exercise, treat the last quarter as April 1, 2025 through June 30, 2025. Include only confirmed bookings, count bookings by destination within each segment, and return exactly three destinations per segment when available.

Output

One row per customer segment and destination, with segment_name, destination_name, booking_count, and destination_rank.
Include only the top three destinations for each segment. Break equal booking counts alphabetically by destination name.
Order by segment_name, then destination_rank.

Schema

customer_segments

Column	Type	Description
segment_idPK	INT	Unique customer segment identifier
segment_name	VARCHAR(50)	Customer segment name

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
segment_id	INT	Associated customer segment

destinations

Column	Type	Description
destination_idPK	INT	Unique hotel destination identifier
destination_name	VARCHAR(100)	Hotel destination name
region	VARCHAR(50)	Geographic region

hotel_bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
customer_id	INT	Customer who made the booking
destination_id	INT	Booked hotel destination
booked_at	DATE	Booking date
booking_status	VARCHAR(20)	Booking lifecycle status

Tablescustomer_segmentscustomersdestinationshotel_bookings