Top Brands by Spend | Dataford Interview Questions

Given a table of user receipt scans, write a query to find the top three brand partners by total spend for each user segment over the last quarter at Fetch.

Assume the last quarter is the completed calendar quarter relative to the current date. Exclude scans that cannot be associated with a user segment or brand partner.

Output

One row per qualifying segment and brand partner, with columns segment, brand_name, total_spend, and segment_rank.
Include no more than three brands per segment, ordered by segment, rank, total spend descending, and brand name ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Fetch user identifier
segment	VARCHAR(50)	User segmentation label

receipt_scans

Column	Type	Description
scan_idPK	INT	Unique receipt scan identifier
user_id	INT	User who submitted the scan
brand_id	INT	Brand identified on the receipt
spend_amount	DECIMAL(12,2)	Purchase amount represented by the scan
scanned_at	DATE	Date the receipt was scanned

brand_partners

Column	Type	Description
brand_idPK	INT	Unique brand partner identifier
brand_name	VARCHAR(100)	Brand partner name

Your question is Top Brands by Spend. Start with the requirements and the three 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