Optimize SQL for Scale | Dataford Interview Questions

Explain your approach to optimizing SQL queries for large-scale datasets.

Use the supplied PostgreSQL schema and data to produce an efficient revenue summary query. The solution should preserve the required filtering and matching behavior while addressing performance considerations in your explanation.

Output

One row per customer region and product category with positive completed revenue from January through March 2025.
Columns: region, category, total_revenue, and sale_count.
Order by total_revenue descending, then region ascending and category ascending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
region	VARCHAR(50)	Customer geographic region
segment	VARCHAR(30)	Customer market segment

products

Column	Type	Description
product_idPK	INT	Unique product identifier
category	VARCHAR(60)	Product category
product_name	VARCHAR(100)	Product name

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
customer_id	INT	Referenced customer identifier
product_id	INT	Referenced product identifier
sale_date	DATE	Date of the sale
amount	NUMERIC(12,2)	Sale amount
status	VARCHAR(20)	Sale processing status

Your question is Optimize SQL for Scale. Start with the requirements and the three 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