First-Time Bookings Monthly Report | Dataford Interview Questions

Given a transaction (bookings) table, develop three business metrics in SQL, then parameterize the solution; produce a monthly report of unique users who booked for the first time, including months missing from the fact table.

Asked in the Tech round 2 (SQL) stage. Candidate used a recursive CTE date spine. Reported follow-up: What data quality checks would you apply to this data?

Output

One row per calendar month in the parameterized range, including months with no qualifying bookings.
Columns: month_start, total_bookings, unique_users, first_time_users.
Include confirmed bookings with non-null users, and order by month_start ascending.

Schema

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
user_id	INT	User who made the booking
booked_at	TIMESTAMP	Timestamp when the booking was created
status	VARCHAR(20)	Booking status

Your question is First-Time Bookings Monthly Report. Start with the requirements and the one table 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

Asked in the Tech round 2 (SQL) stage. Candidate used a recursive CTE date spine. Reported follow-up: What data quality checks would you apply to this data?

Output

One row per calendar month in the parameterized range, including months with no qualifying bookings.
Columns: month_start, total_bookings, unique_users, first_time_users.
Include confirmed bookings with non-null users, and order by month_start ascending.

Schema

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
user_id	INT	User who made the booking
booked_at	TIMESTAMP	Timestamp when the booking was created
status	VARCHAR(20)	Booking status

Your question is First-Time Bookings Monthly Report. Start with the requirements and the one table 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.