Debug and Optimize Slow SQL | Dataford Interview Questions

A reporting query at a commerce platform summarizes recent orders for active customers, but the production version is slow. Write a PostgreSQL query that returns the top active customers by order revenue since January 1, 2024, while also providing an execution plan that can be inspected for scans, joins, aggregation, sorting, and buffer usage.

Output

Return the PostgreSQL execution plan for the query.
The underlying result contains one row per active customer with orders on or after January 1, 2024, including customer_id, order_count, and total_amount.
Sort customers by total_amount descending and return at most 10 customers.

Schema

customers

Column	Type	Description
customer_idPK	BIGINT	Unique customer identifier
customer_name	VARCHAR(255)	Customer display name
is_active	BOOLEAN	Whether the customer is currently active
signup_date	DATE	Date the customer account was created

orders

Column	Type	Description
order_idPK	BIGINT	Unique order identifier
customer_id	BIGINT	Customer who placed the order
order_date	DATE	Date the order was placed
total_amount	NUMERIC(12,2)	Order total amount

Your question is Debug and Optimize Slow SQL. 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