Rolling 30-Day Applications Average | Dataford Interview Questions

United Auto Credit wants a daily view of loan application volume across active dealerships. Write a PostgreSQL query that calculates the rolling 30-day average of daily applications.

Requirements

Count applications by calendar day using submitted_at, including days with zero applications.
Include applications submitted through active dealerships only.
Calculate the average daily application count for the current day and preceding 29 calendar days using a window function.
Return one row per day, ordered chronologically, with the application date, daily count, and rolling average.

Schema

loan_applications

Column	Type	Description
application_idPK	INT	Unique loan application identifier
dealership_id	INT	References the dealership associated with the application
submitted_at	DATE	Date the application was submitted

dealerships

Column	Type	Description
dealership_idPK	INT	Unique dealership identifier
dealership_name	VARCHAR(100)	Dealership name
region	VARCHAR(50)	United Auto Credit operating region
is_active	BOOLEAN	Whether the dealership is currently active

Your question is Rolling 30-Day Applications Average. 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