Booking Conversion SQL Analysis | Dataford Interview Questions

Write a SQL query to analyze booking conversion by city and week, including joins between users, sitters, and bookings tables for Rover.

Treat each booking row as a booking opportunity and confirmed bookings as conversions. Use the sitter's city and the booking request timestamp to define the reporting dimensions.

Output

One row per city and calendar week.
Columns: city, week_start, booking_requests, confirmed_bookings, and conversion_rate.
Include only bookings with a non-null request timestamp and matching user and sitter records. Sort by city, then week_start.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Rover user identifier
full_name	VARCHAR(100)	User's display name
email	VARCHAR(255)	User email address

sitters

Column	Type	Description
sitter_idPK	INT	Unique Rover sitter identifier
sitter_name	VARCHAR(100)	Sitter's display name
city	VARCHAR(100)	City where the sitter offers services

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
user_id	INT	User who requested the booking
sitter_id	INT	Sitter associated with the booking
requested_at	TIMESTAMP	Timestamp when the booking was requested
status	VARCHAR(30)	Booking lifecycle status

Your question is Booking Conversion SQL Analysis. 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