Rolling 7-Day Active Supply | Dataford Interview Questions

Given a table of daily driver earnings, write a query to calculate the rolling 7-day average of active driver supply by city for Uber Drivers.

Active driver supply is the number of distinct drivers with a row for a city and date. Treat dates without active drivers as zero supply, and calculate averages over calendar days. For the first six dates, average all available dates.

Output

One row per city and calendar date.
Columns: city, activity_date, and rolling_7_day_avg_supply.
Round the average to two decimal places and order by city, then activity_date.

Schema

daily_driver_earnings

Column	Type	Description
earning_idPK	INT	Unique earning record identifier
earning_date	DATE	Date of the driver earning record
city	VARCHAR(100)	Uber Drivers operating city
driver_id	VARCHAR(50)	Driver identifier
daily_earnings	DECIMAL(10,2)	Driver earnings for the date

Your question is Rolling 7-Day Active Supply. 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

Given a table of daily driver earnings, write a query to calculate the rolling 7-day average of active driver supply by city for Uber Drivers.

Output

One row per city and calendar date.
Columns: city, activity_date, and rolling_7_day_avg_supply.
Round the average to two decimal places and order by city, then activity_date.

Schema

daily_driver_earnings

Column	Type	Description
earning_idPK	INT	Unique earning record identifier
earning_date	DATE	Date of the driver earning record
city	VARCHAR(100)	Uber Drivers operating city
driver_id	VARCHAR(50)	Driver identifier
daily_earnings	DECIMAL(10,2)	Driver earnings for the date

Your question is Rolling 7-Day Active Supply. 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.