Rank Users With SQL Window Functions | Dataford Interview Questions

Basis Technologies wants to compare user revenue performance across countries in Basis DSP. Write a PostgreSQL query that aggregates credited revenue per user and ranks users against others in the same country.

Requirements

Include every user, including users with no credited revenue.
Calculate each user's total credited revenue.
Rank users within each country by total revenue in descending order. Users with tied revenue must receive the same rank.
Return results ordered by country, rank, and user ID.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	User display name
country	VARCHAR(2)	Two-letter user country code

revenue_events

Column	Type	Description
event_idPK	INTEGER	Unique revenue event identifier
user_id	INTEGER	User associated with the revenue event
amount	NUMERIC(12,2)	Revenue amount
event_status	VARCHAR(20)	Revenue event processing status

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