SQL Moving Average With Windows | Dataford Interview Questions

J.P. Morgan Chase wants a transaction trend view for customer analytics. Write a PostgreSQL query that calculates a 30-day moving average of each customer’s daily transaction total.

Use the current transaction date and the preceding 29 calendar days, so the window covers 30 calendar days in total. Average only days represented in the aggregated transaction data, and ignore NULL transaction amounts through PostgreSQL aggregate behavior.

Requirements

Join customers to transactions and exclude transactions without a matching customer.
Aggregate multiple transactions for the same customer and date into one daily_amount.
Calculate the customer-level moving average with a window function using a 30-day calendar frame.
Return results ordered by customer ID and transaction date.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
segment	VARCHAR(30)	Customer segment

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
customer_id	INTEGER	References customers.customer_id
transaction_date	DATE	Date of the transaction
amount	NUMERIC(12,2)	Transaction amount

Your question is SQL Moving Average With Windows. 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