SQL Window Functions for Revenue | Dataford Interview Questions

Your question is SQL Window Functions for Revenue. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Devoteam's analytics team needs a cohort-level view of user revenue for its data platform. Write a PostgreSQL query that aggregates completed transaction revenue per user, then calculates both revenue rank and cumulative revenue within each signup-month cohort.

Requirements

Derive each user's cohort from the month of signup_date.
Include users with no completed transactions, treating their revenue as zero.
Use window functions to calculate revenue_rank within each cohort and running_total ordered by revenue descending, with user_id as a deterministic tie-breaker.
Exclude users whose signup date is unknown and transactions that are not completed.

Schema

users

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

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User associated with the transaction
amount	NUMERIC(10,2)	Revenue amount
status	VARCHAR(20)	Transaction processing status