Rolling 7-Day Price Freeze Conversion | Dataford Interview Questions

Write a query to calculate the rolling 7-day conversion rate of users who utilized the Price Freeze feature at Hopper.

Assume a user converts when they make at least one booking within seven calendar days after using Price Freeze, including the utilization date. Calculate each utilization date's rate using that date and the preceding six calendar days.

Output

One row per date with at least one Price Freeze utilization.
Columns: freeze_date, rolling_converted_users, rolling_utilized_users, and rolling_conversion_rate.
Return the conversion rate as a percentage rounded to two decimal places, ordered by freeze_date ascending.

Schema

price_freeze_uses

Column	Type	Description
freeze_use_idPK	INT	Unique Price Freeze usage identifier
user_id	VARCHAR(20)	Hopper user who used Price Freeze
freeze_date	DATE	Date of Price Freeze utilization

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
user_id	VARCHAR(20)	Hopper user who made the booking
booking_date	DATE	Date the booking was made

Your question is Rolling 7-Day Price Freeze Conversion. 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