Join-Heavy SQL Practice | Dataford Interview Questions

Solve a set of join-heavy SQL questions under time pressure.

Using the supplied Airbnb host, listing, and reservation data, write a query that summarizes each host with at least one active listing. Include only confirmed reservations whose checkout dates fall in 2025. Hosts without qualifying reservations must still appear.

Output

One row per host with at least one active listing.
Columns: host_id, host_name, active_listing_count, reservation_count, gross_booking_value, and latest_checkout.
Order by gross_booking_value descending, then host_id ascending.

Schema

hosts

Column	Type	Description
host_idPK	INT	Unique host identifier
host_name	VARCHAR(100)	Display name of the host

listings

Column	Type	Description
listing_idPK	INT	Unique listing identifier
host_id	INT	Host who owns the listing
listing_name	VARCHAR(150)	Listing title
is_active	BOOLEAN	Whether the listing is currently active

reservations

Column	Type	Description
reservation_idPK	INT	Unique reservation identifier
listing_id	INT	Listing associated with the reservation
status	VARCHAR(20)	Reservation status
checkout_date	DATE	Reservation checkout date
total_amount	NUMERIC(10,2)	Total reservation amount

Your question is Join-Heavy SQL Practice. Start with the requirements and the three 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