Rank Users With Window Functions | Dataford Interview Questions

Your question is Rank Users With Window Functions. 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

Brooksource wants a regional view of user spending across its analytics platform. Write a PostgreSQL query that calculates each user's completed purchase total for 2025 and ranks users within their region.

Requirements

Include every user, including users with no qualifying purchases, with their total spend shown as zero.
Use RANK() partitioned by region and ordered by total spend descending. Users tied on spend must share the same rank.
Include only purchases with status completed and a purchase date in 2025.
Return results ordered by region, total spend descending, and user ID as a deterministic tie-breaker.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User's full name
region	VARCHAR(50)	User's assigned region

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
user_id	INT	User who made the purchase
purchase_date	DATE	Date the purchase was recorded
amount	NUMERIC(10,2)	Purchase amount
status	VARCHAR(20)	Purchase processing status