Rank Users by Revenue | Dataford Interview Questions

EverC needs a regional leaderboard for users based on revenue recorded across its platform. Write a PostgreSQL query that calculates each user's total revenue and assigns a position within their region.

Include revenue adjustments in each user's total. Users whose total revenue is zero, negative, or not available should not appear in the leaderboard.

Output

One row per qualifying user, with columns region, user_id, user_name, total_revenue, and revenue_rank.
Include only users whose total revenue is positive.
Rank users separately within each region, with the highest total revenue first.
Resolve equal totals by ascending user_id so the output is deterministic.
Order the final rows by region, then revenue_rank, then user_id.

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
user_name	VARCHAR(100)	User display name
region	VARCHAR(50)	User's regional market

user_revenue

Column	Type	Description
revenue_idPK	INT	Primary key for the revenue event
user_id	INT	User associated with the revenue event
revenue_date	DATE	Date the revenue was recorded
amount	DECIMAL(10,2)	Revenue amount, including adjustments
source	VARCHAR(50)	Revenue source label

Your question is Rank Users by Revenue. 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