How would you optimize a slow-performing SQL query?
Use the supplied PostgreSQL schema and data to demonstrate an optimized reporting query. Return monthly completed transaction metrics for active accounts, include the latest review status when available, and calculate the previous reported month and percentage change.
Output
- One row per active account and month with completed transactions.
- Columns:
account_id, account_name, month_start, transaction_count, total_amount, prior_month_amount, percent_change, and latest_review_status.
- Order by
account_id, then month_start ascending. Use NULL when no prior month or review exists.
Output details
- Restrict transactions to
occurred_at >= DATE '2025-01-01' and < DATE '2025-04-01'.
- Select the latest review by
reviewed_at descending, breaking ties with review_id descending.
- Set
percent_change to NULL when prior_month_amount is NULL or zero.
- Calculate
percent_change using the absolute value of prior_month_amount as the denominator and round it to 2 decimal places.
