7-Day Rolling Transactions | Dataford Interview Questions

Featurespace's ARIC Risk Hub analyzes transaction activity by user. Write a PostgreSQL query that calculates a 7-day rolling average of daily transaction counts for each active user during the reporting period from 2024-01-01 through 2024-01-10.

Requirements

Generate one row for every active user and every calendar day in the reporting period, including days with no transactions.
Count transactions per user per day, treating missing activity as zero.
Use a window function partitioned by user and ordered by date to calculate the rolling average over the current day and previous six calendar days.
Return only dates from 2024-01-04 onward, ordered by user and activity date.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	User display name
risk_segment	VARCHAR(30)	Featurespace risk classification
email	VARCHAR(150)	Contact email

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
user_id	INTEGER	User associated with the transaction
transaction_timestamp	TIMESTAMP	Transaction event timestamp
channel	VARCHAR(30)	Transaction channel
amount	NUMERIC(12,2)	Transaction amount

Your question is 7-Day Rolling Transactions. 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