Optimize PostgreSQL Merchant Ranking Query

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

1. One row per qualifying merchant ranked within its category.
2. Columns: category, category_rank, merchant_id, merchant_name, total_spend, transaction_count, latest_transaction_date.
3. 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.
4. Order by category, category_rank, then merchant_id.

Output details

• Treat the date range as inclusive of 2025-01-01 and exclusive of 2025-07-01.
• Use dense ranking by descending total_spend, so merchants tied on spend share the same rank.
• Include every qualifying transaction row without deduplication; count rows with COUNT(*).
• Let SUM(amount) ignore NULL amounts and perform no rounding of total_spend.