Optimizing Slow SQL Queries | Dataford Interview Questions

Your question is Optimizing Slow SQL Queries. Start with the requirements and the three 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.

Problem

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.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_status	VARCHAR(20)	Current account status
opened_date	DATE	Date the account was opened

transactions

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
transaction_date	DATE	Date of the transaction
transaction_status	VARCHAR(20)	Processing status of the transaction
amount	NUMERIC(12,2)	Transaction amount

merchants

Column	Type	Description
merchant_idPK	INT	Unique merchant identifier
merchant_name	VARCHAR(100)	Merchant display name
category	VARCHAR(50)	Merchant spending category