Rolling Average Payroll Volume | Dataford Interview Questions

Write a SQL window function query to compute a 7-day rolling average of payroll submission volume by business category at Gusto.

Use active business categories and evaluate the date range from January 1 through January 10, 2024. Include dates with no submissions, and calculate averages over the available dates at the beginning of the range.

Output

One row per active business category and calendar date in the range.
Columns: category_name, submission_date, daily_submission_volume, and rolling_7_day_average.
Include zero-volume dates and order by category_name, then submission_date.

Schema

business_categories

Column	Type	Description
category_idPK	INT	Unique business category identifier
category_name	VARCHAR(100)	Business category name
is_active	BOOLEAN	Whether the category is currently active

payroll_submissions

Column	Type	Description
submission_idPK	INT	Unique payroll submission identifier
category_id	INT	Business category associated with the submission
submitted_at	TIMESTAMP	Timestamp when the payroll was submitted

Your question is Rolling Average Payroll Volume. 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