First Night Booking Flag | Dataford Interview Questions

Given a bookings table and a listings attribute table (e.g. country), write SQL to compute how many listings were booked for the first time ever on each day.

Asked in the technical phone screen stage. The table setup is visible in the post, and the first booked per day metric was confirmed by the OP in a reply.

Output

One row per booking date where at least one listing was booked for the first time ever
Columns: booking_date, first_time_booked_listings
Count each listing only on the date of its earliest booking, and ignore later bookings for that same listing
Sort by booking_date ascending

Schema

bookings

Column	Type	Description
booking_idPK	BIGINT	Unique booking record identifier
listing_id	BIGINT	Identifier of the listing that was booked
booking_date	DATE	Date when the booking was created
guest_id	BIGINT	Identifier of the guest who made the booking

listings_attributes

Column	Type	Description
listing_idPK	BIGINT	Unique listing identifier
country	VARCHAR(64)	Listing country
city	VARCHAR(128)	Listing city

Your question is First Night Booking Flag. 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