Rank Top 3 Users Per Region | Dataford Interview Questions

Chevron's analytics team wants to identify the highest-revenue users in each operating region. Write a PostgreSQL query that aggregates transaction revenue by user, ranks users within their region, and returns the top three users per region.

Requirements

Include users with no transactions and treat their revenue as zero.
Aggregate all transaction rows for each user before ranking.
Use a window function to rank users independently within each region.
Return the top three users per region, breaking revenue ties by ascending user_id.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
full_name	VARCHAR(100)	User's full name
region_name	VARCHAR(50)	Chevron operating region

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
user_id	INTEGER	User associated with the transaction
revenue	NUMERIC(12,2)	Revenue attributed to the transaction
transaction_date	DATE	Transaction date

Your question is Rank Top 3 Users Per Region. 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