Optimizing Slow SQL or Spark | Dataford Interview Questions

Describe a time you had to optimize a slow-running SQL query or Spark job.

Using the provided PostgreSQL tables, rewrite the slow SQL pattern to return the required results efficiently. Explain how you would validate the improvement and identify useful indexes.

Output

One row per active store, including stores with no qualifying transactions.
Columns: store_id, store_name, completed_transaction_count, and completed_revenue.
Include completed transactions from January 1, 2025 through March 31, 2025, sort by revenue descending, then store_id ascending.

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store display name
region	VARCHAR(50)	Geographic region
is_active	BOOLEAN	Whether the store is currently active

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
store_id	INT	Store associated with the transaction
transaction_date	DATE	Transaction date
status	VARCHAR(20)	Transaction processing status
amount	NUMERIC(10,2)	Transaction amount

Your question is Optimizing Slow SQL or Spark. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem