Top 3 Items Per Region Query | Dataford Interview Questions

The Apexon Data & AI platform tracks high-volume user transactions across regions. Write a PostgreSQL query that identifies the three most purchased items in each region during the last 30 days, using completed transactions only.

Output

Return one row per item included in the regional top three, with columns region, item_id, item_name, purchased_units, and region_rank.
Exclude transactions outside the inclusive 30-day interval ending on 2026-08-29, transactions that are not completed, transactions with non-positive or NULL quantities, and users whose region is NULL.
Aggregate purchased units by region and item, rank items independently within each region, and return no more than three rows per region.
Break quantity ties by ascending item_id, and order the final result by region and region_rank.

Schema

transactions

Column	Type	Description
transaction_idPK	BIGINT	Unique transaction identifier
user_id	BIGINT	References users.user_id
item_id	BIGINT	References items.item_id
quantity	INTEGER	Number of units in the transaction
occurred_at	TIMESTAMP	Timestamp when the transaction occurred
status	VARCHAR(20)	Transaction processing status

users

Column	Type	Description
user_idPK	BIGINT	Unique user identifier
region	VARCHAR(40)	Geographic region assigned to the user

items

Column	Type	Description
item_idPK	BIGINT	Unique item identifier
item_name	VARCHAR(100)	Display name of the purchased item

Your question is Top 3 Items Per Region Query. 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