Rolling Cohort Query | Dataford Interview Questions

Write a query to identify top-performing restaurant cohorts over a rolling 30-day window.

Treat a cohort as the calendar month of a restaurant's launch date. Include completed orders with non-null order totals, and evaluate each rolling window ending on a date with at least one qualifying order. Each window includes its ending date and the previous 29 days.

Output

One row per top-ranked cohort and window-ending date, with columns window_end, cohort_month, cohort_revenue, completed_orders, and performance_rank.
Include all cohorts tied for the highest revenue in each window. Order by window_end, then cohort_month.

Schema

restaurants

Column	Type	Description
restaurant_idPK	INT	Unique restaurant identifier
restaurant_name	VARCHAR(100)	Restaurant display name
launched_at	DATE	Restaurant launch date used to define its cohort

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
restaurant_id	INT	Restaurant associated with the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order lifecycle status
order_total	DECIMAL(10,2)	Order revenue amount

Your question is Rolling Cohort Query. 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