SQL: Avg Booking Value by Segment | Dataford Interview Questions

Write a SQL query using joins and aggregates to calculate the average booking value for users who purchased Price Lock compared to those who did not at Hopper.

Use the provided users, bookings, and price_lock_purchases tables. Booking rows with a NULL booking_value do not contribute to the average.

Output

One row per Price Lock status.
Columns: price_lock_status and average_booking_value.
Include purchased and non-purchased users, round the average to two decimal places, and order purchased users first.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Hopper user identifier
email	VARCHAR(150)	User email address
signup_date	DATE	Date the user registered

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
user_id	INT	User who made the booking
booking_value	NUMERIC(12,2)	Booking value in US dollars
booking_date	DATE	Date of booking

price_lock_purchases

Column	Type	Description
purchase_idPK	INT	Unique Price Lock purchase identifier
user_id	INT	User who purchased Price Lock
purchased_at	DATE	Date of Price Lock purchase

Your question is SQL: Avg Booking Value by Segment. 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