Running 7-Day Transaction Average | Dataford Interview Questions

Plaid wants to monitor transaction activity for users connected through Plaid Transactions. Write a PostgreSQL query that calculates a calendar-aware rolling 7-day average of daily transaction counts for each active user.

Requirements

Count transactions per user for every calendar date between the minimum and maximum transaction dates.
Include dates with zero transactions so the average represents seven calendar days.
Use a window function partitioned by user and ordered by date, then return results by user and date.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Plaid user identifier
user_name	VARCHAR(100)	User display name
account_status	VARCHAR(20)	User account status

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User associated with the transaction
transaction_date	DATE	Transaction posting date
merchant_name	VARCHAR(100)	Merchant associated with the transaction
amount	NUMERIC(10,2)	Transaction amount

Your question is Running 7-Day Transaction 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