Rank Top Users by Spend | Dataford Interview Questions

Hirebeat wants to identify its highest-spending users in each country. Write a PostgreSQL query that calculates each user's total purchase spend, ranks users within their country, and returns the top three users per country.

Requirements

Join users to purchases and calculate total spend per user.
Include users with no purchases, treating their total spend as 0.
Use a window function to rank users independently within each country.
Return at most three users per country, ordered by country and descending total spend. Break spending ties with the smaller user_id.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Hirebeat user identifier
user_name	VARCHAR(100)	User display name
country	VARCHAR(50)	User country

purchases

Column	Type	Description
purchase_idPK	INTEGER	Unique purchase identifier
user_id	INTEGER	Identifier of the purchasing user
amount	NUMERIC(10,2)	Purchase amount

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