Optimize Slow Partitioned Query | Dataford Interview Questions

How would you optimize a slow-running query that scans millions of rows across multiple partitioned tables?

Provide a PostgreSQL query that returns the required aggregates for the supplied date range. Keep the result logically correct while demonstrating choices appropriate for large partitioned datasets.

Output

One row per calendar month and region for active enterprise customers with completed, positive-value sales from January 1 through March 31, 2025.
Columns: month_start, region_name, total_revenue, order_count, and distinct_customers.
Sort by month_start ascending, then region_name ascending.

Schema

sales_events

Column	Type	Description
event_idPK	INT	Unique sales event identifier
order_id	VARCHAR(20)	Business order identifier
customer_id	INT	Customer associated with the event
event_date	DATE	Partition key and sales date
amount	NUMERIC(12,2)	Value of the sales event
status	VARCHAR(20)	Sales event status

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
segment	VARCHAR(30)	Customer market segment
region_id	INT	Customer region
is_active	BOOLEAN	Whether the customer is currently active

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(60)	Region display name
country_code	VARCHAR(3)	Country or territory code

Your question is Optimize Slow Partitioned Query. 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