Your question is Top Clients by Volume per Region. 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.
You’re a data engineer at a fintech payments processor that settles card and ACH payments for tens of thousands of SMB and mid-market clients across multiple geographic regions. The revenue team runs quarterly business reviews (QBRs) and wants to identify the most important clients in each region by transaction volume (count of transactions) in the most recently completed calendar quarter. This output feeds an executive dashboard and also triggers account-management workflows, so accuracy around quarter boundaries and tie-handling matters.
Write a SQL query to return the top 3 clients by transaction volume for each region in the last completed quarter.
status = 'SETTLED'.CURRENT_DATE (e.g., if today is 2026-02-13, last quarter is 2025 Q4: 2025-10-01 through 2025-12-31).COUNT(*) of settled transactions per (region, client_id) in that quarter.txn_count DESC, then total_amount_usd DESC, then client_id ASC.region, client_id, client_name, txn_count, total_amount_usd, rank_in_region.| Column | Type | Description |
|---|---|---|
| region_idPK | INT | Primary key |
| region | VARCHAR(50) | Region name (e.g., 'NA', 'EMEA') |
| Column | Type | Description |
|---|---|---|
| client_idPK | INT | Primary key |
| client_name | VARCHAR(255) | Legal/business name |
| region_id | INT | Foreign key to regions.region_id |
| onboarded_at | DATE | Date client started processing |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | BIGINT | Primary key |
| client_id | INT | Foreign key to clients.client_id |
| created_at | TIMESTAMP | Transaction creation timestamp |
| amount_usd | DECIMAL(12,2) | Amount in USD |
| status | VARCHAR(20) | Transaction status (e.g., SETTLED, DECLINED) |