Your question is Optimize Slow Query on Transactions. Start with the requirements and the four 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.
How do you optimize a query that is running slowly on a massive financial transaction dataset?
Using the supplied PostgreSQL schema, write an efficient query that summarizes posted transactions during the first quarter of 2025. Include only active accounts with matching customers and merchants.
customer_name, category, month_start, transaction_count, total_amount, prior_month_total| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer display name |
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Unique account identifier |
| customer_id | INT | Owning customer identifier |
| account_status | VARCHAR(20) | Current account status |
| Column | Type | Description |
|---|---|---|
| merchant_idPK | INT | Unique merchant identifier |
| merchant_name | VARCHAR(100) | Merchant name |
| category | VARCHAR(50) | Merchant spending category |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| account_id | INT | Account associated with the transaction |
| merchant_id | INT | Merchant associated with the transaction |
| occurred_at | TIMESTAMP | Transaction timestamp |
| amount | NUMERIC(14,2) | Signed transaction amount |
| status | VARCHAR(20) | Transaction processing status |
| transaction_type | VARCHAR(30) | Transaction classification |