Running 30-Day Average in SQL | Dataford Interview Questions

Your question is Running 30-Day Average in SQL. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

GDIT's mission analytics dashboard needs a rolling view of completed transaction activity. Write a PostgreSQL query that calculates the 30-day moving average of daily completed transaction volume.

Requirements

Count only transactions where status = 'completed'.
Aggregate transactions by transaction_date before applying the window function.
Calculate an average over the current date and the preceding 29 calendar days, producing a 30-day inclusive window.
Return one row per date with completed transactions, ordered chronologically.

Schema

mission_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
transaction_date	DATE	Date the transaction occurred
status	VARCHAR(20)	Transaction state, such as completed, pending, or cancelled
transaction_type	VARCHAR(30)	Operational transaction category
amount	NUMERIC(12,2)	Transaction amount

Your question is Running 30-Day Average in SQL. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

GDIT's mission analytics dashboard needs a rolling view of completed transaction activity. Write a PostgreSQL query that calculates the 30-day moving average of daily completed transaction volume.

Requirements

Count only transactions where status = 'completed'.
Aggregate transactions by transaction_date before applying the window function.
Calculate an average over the current date and the preceding 29 calendar days, producing a 30-day inclusive window.
Return one row per date with completed transactions, ordered chronologically.

Schema

mission_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
transaction_date	DATE	Date the transaction occurred
status	VARCHAR(20)	Transaction state, such as completed, pending, or cancelled
transaction_type	VARCHAR(30)	Operational transaction category
amount	NUMERIC(12,2)	Transaction amount