Top Purchases Per Category | Dataford Interview Questions

Your question is Top Purchases Per Category. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

AARP Rewards wants to identify the highest-value completed purchases within each user category. Write a PostgreSQL query that joins users to transactions, filters eligible purchases, and returns the top three purchases for every category.

Requirements

Include only transactions with status = 'completed' and a positive, non-null amount.
Rank purchases separately within each user_category, using purchase amount descending and transaction_id ascending as the deterministic tie-breaker.
Return no more than three purchases per category, including the user, category, transaction, amount, and rank.
Sort the final result by category, rank, and transaction ID.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	User display name
user_category	VARCHAR(30)	AARP Rewards user category

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
user_id	INTEGER	User who made the purchase
amount	NUMERIC(10,2)	Purchase amount in dollars
status	VARCHAR(20)	Transaction processing status