Rank Customers With Window SQL | Dataford Interview Questions

Citadel Data Platform needs a regional view of customer purchase activity for the first quarter of 2025. Write a PostgreSQL query that aggregates qualifying purchases and ranks customers within their region.

Requirements

Include only active customers with a non-null region.
Sum only purchases with status = 'completed' and a purchase_date from 2025-01-01 inclusive through 2025-03-31 inclusive.
Include customers with no qualifying purchases, assigning them a total of zero.
Use RANK() partitioned by region and order the final results by region, rank, and customer ID.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
region	VARCHAR(50)	Customer geographic region
is_active	BOOLEAN	Whether the customer is currently active

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer associated with the purchase
purchase_date	DATE	Date on which the purchase was recorded
purchase_amount	NUMERIC(12,2)	Monetary value of the purchase
status	VARCHAR(20)	Purchase processing status

Your question is Rank Customers With Window SQL. 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