Window Functions for Running Totals | Dataford Interview Questions

NewMarket wants to evaluate user engagement by signup cohort. Write a PostgreSQL query that summarizes qualifying transactions by user and calendar month, calculates each user's cumulative spend, and ranks users within their signup cohort.

Requirements

Derive cohort_month from users.signup_date and activity_month from transactions.transaction_date.
Include only transactions on or after the user's signup date. Exclude transactions that cannot be matched to a user.
Aggregate transaction amounts by user and activity month.
Calculate a per-user running total ordered by activity month, and rank users within each cohort by total spend descending. Preserve ties with the same rank.
Return one row per user and activity month, ordered by cohort, activity month, and user ID.

Schema

users

Column	Type	Description
user_idPK	INT	Unique NewMarket user identifier
user_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user joined NewMarket

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User associated with the transaction
transaction_date	DATE	Date of the transaction
amount	NUMERIC(10,2)	Transaction amount

Your question is Window Functions for Running Totals. 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