Business Context
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.
Task
Write a SQL query to return the top 3 clients by transaction volume for each region in the last completed quarter.
Requirements
- Consider only transactions with
status = 'SETTLED'.
- Define last quarter as the most recently completed calendar quarter relative to
CURRENT_DATE (e.g., if today is 2026-02-13, last quarter is 2025 Q4: 2025-10-01 through 2025-12-31).
- Compute transaction volume as
COUNT(*) of settled transactions per (region, client_id) in that quarter.
- Return exactly 3 rows per region when possible. If a region has fewer than 3 clients with settled transactions in the quarter, return all of them.
- Break ties deterministically by ordering by
txn_count DESC, then total_amount_usd DESC, then client_id ASC.
- Output columns:
region, client_id, client_name, txn_count, total_amount_usd, rank_in_region.
Table Definitions
regions
| column | type | description |
|---|
| region_id | INT | Primary key |
| region | VARCHAR(50) | Region name (e.g., 'NA', 'EMEA') |
clients
| column | type | description |
|---|
| client_id | 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 |
transactions
| column | type | description |
|---|
| transaction_id | 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) | 'SETTLED', 'DECLINED', 'REVERSED', etc. |
Sample Data
regions
clients
| client_id | client_name | region_id | onboarded_at |
|---|
| 101 | Acme Retail LLC | 1 | 2023-04-10 |
| 102 | Bluebird Logistics | 1 | 2024-01-05 |
| 103 | Cedar Health Group | 1 | 2024-06-20 |
| 201 | Delta Travel GmbH | 2 | 2022-11-02 |
| 202 | Ember Marketplace Ltd | 2 | 2024-03-15 |
transactions
Assume CURRENT_DATE = '2026-02-13' so last quarter is 2025-10-01 to 2025-12-31.
| transaction_id | client_id | created_at | amount_usd | status |
|---|
| 9001 | 101 | 2025-10-05 10:15:00 | 120.00 | SETTLED |
| 9002 | 101 | 2025-11-18 09:00:00 | 80.00 | SETTLED |
| 9003 | 102 | 2025-12-02 14:30:00 | 500.00 | SETTLED |
| 9004 | 103 | 2025-12-15 08:10:00 | 60.00 | DECLINED |
| 9005 | 201 | 2025-10-20 16:45:00 | 200.00 | SETTLED |
| 9006 | 201 | 2025-12-29 12:00:00 | 150.00 | SETTLED |
| 9007 | 202 | 2025-11-01 11:11:00 | 300.00 | SETTLED |
Expected Output (from sample data)
| region | client_id | client_name | txn_count | total_amount_usd | rank_in_region |
|---|
| EMEA | 201 | Delta Travel GmbH | 2 | 350.00 | 1 |
| EMEA | 202 | Ember Marketplace Ltd | 1 | 300.00 | 2 |
| NA | 101 | Acme Retail LLC | 2 | 200.00 | 1 |
| NA | 102 | Bluebird Logistics | 1 | 500.00 | 2 |
