How do you optimize a SQL query that is performing poorly on a large dataset?
Using the provided PostgreSQL schema, rewrite the query so it returns the required results efficiently. Consider filtering, join behavior, aggregation, ranking, and the indexes or execution-plan evidence you would use to validate the improvement.
Output
- One row per qualifying merchant ranked within its category.
- Columns:
category, category_rank, merchant_id, merchant_name, total_spend, transaction_count, latest_transaction_date.
- Include only active accounts, approved transactions from 2025-01-01 through 2025-06-30, non-null merchant categories, and merchants ranked 1 or 2 within their category.
- Order by
category, category_rank, then merchant_id.
