Top Users by Monthly Purchases | Dataford Interview Questions

Write a SQL query to find the top 5 users by total number of purchases in each month.

Use the users and purchases tables. Include only users with matching purchase records. If users tie on purchase count, order the tied users by user_id ascending.

Output

One row per included user and month, with columns purchase_month, user_id, and purchase_count.
Return no more than five users per month.
Order by purchase_month ascending, purchase_count descending, and user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
username	VARCHAR(100)	User display name

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
user_id	INT	User who made the purchase
purchased_at	TIMESTAMP	Timestamp when the purchase occurred

Your question is Top Users by Monthly Purchases. 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