SQL Window Functions for Rolling Metrics | Dataford Interview Questions

Snapmint's risk team wants to monitor daily loan defaults and smooth short-term fluctuations. Write a PostgreSQL query that calculates the 30-day rolling average of daily default events for the reporting period from February 1 through February 10, 2025.

Requirements

Join loan_defaults to loans so only defaults belonging to known loans are counted.
Include every calendar date in the reporting period, including dates with zero defaults.
Calculate the daily default count and the rolling average across the current date and previous 29 calendar days.
Round the rolling average to two decimal places and order the result chronologically.

Schema

loans

Column	Type	Description
loan_idPK	INT	Unique Snapmint loan identifier
customer_id	VARCHAR(20)	Snapmint customer identifier
product_name	VARCHAR(50)	Financing product associated with the loan

loan_defaults

Column	Type	Description
default_event_idPK	INT	Unique default event identifier
loan_id	INT	Loan associated with the default event
default_date	DATE	Date the loan entered default
default_amount	NUMERIC(12,2)	Outstanding amount at default

Your question is SQL Window Functions for Rolling Metrics. 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