Your question is Top 3 Items Per Region Query. Start with the requirements and the three 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.
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.
Assume the query runs on 2026-08-29. Count purchased units from completed transactions only. For distributed execution, filter the large transactions table before joining dimension tables, and aggregate before applying the ranking window function.
item_id.| 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | BIGINT | Unique user identifier |
| region | VARCHAR(40) | Geographic region assigned to the user |
| Column | Type | Description |
|---|---|---|
| item_idPK | BIGINT | Unique item identifier |
| item_name | VARCHAR(100) | Display name of the purchased item |