How would you optimize a SQL query for performance?
Using the supplied PostgreSQL schema, write an optimized query that calculates billed totals for qualifying active accounts. The answer should be suitable for explaining how you would validate query performance and reduce unnecessary work.
Output
- One row per active account with completed invoices in the requested date range.
- Columns:
region, account_id, account_name, total_billed, and account_rank.
- Sort by
region ascending with NULL regions last, then account_rank ascending and account_id ascending.
Output details
- Filter
invoice_date from DATE '2025-01-01' inclusive through DATE '2025-04-01' exclusive.
- Rank accounts separately within each
region by total_billed descending using dense ranking, so equal totals share ranks.
- Compute
total_billed as the exact sum of quantity * unit_price, retaining qualifying accounts whose total is zero.
- Preserve SQL NULL behavior for arithmetic and aggregates; do not replace NULL results with zero.
