Rolling Spend With Window Functions | Dataford Interview Questions

The American Express cardmember analytics team needs a monthly view of spending trends for active cardmembers. Write a PostgreSQL query that calculates a rolling three-month average of monthly spend for each active cardmember.

Requirements

Join cardmembers to monthly_card_spend using cardmember_id.
Include only cardmembers whose status is exactly active.
Treat a NULL monthly spend as zero, then calculate the average of the current month and up to the two preceding months for each cardmember using a window function.
Return results in cardmember and chronological month order.

Schema

cardmembers

Column	Type	Description
cardmember_idPK	INTEGER	Unique cardmember identifier
cardmember_name	VARCHAR(100)	Cardmember display name
status	VARCHAR(20)	Current cardmember account status

monthly_card_spend

Column	Type	Description
spend_idPK	INTEGER	Unique monthly spend record
cardmember_id	INTEGER	References cardmembers.cardmember_id
spend_month	DATE	First day of the spend month
monthly_spend	NUMERIC(12,2)	Total cardmember spend for the month

Your question is Rolling Spend With Window Functions. 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