Optimize SQL to minimize scans
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Given a large analytics query, refactor it to minimize table scans.
1) Replace unnecessary CTEs that cause multiple scans with inline aggregations or derived tables where appropriate.
2) Prefer GROUP BY aggregations when applicable and justify the execution plan.
3) Explain how you would verify the number of scans using EXPLAIN and iterate on the plan.
4) Start with a quick draft solution, then refine it to reduce scans and improve readability.
Overview: This question evaluates proficiency in SQL query optimization, specifically minimizing table scans, choosing between CTEs and derived tables, applying aggregation strategies, and interpreting execution plans.
Read the full Meta Data Engineer interview experience this question came from
You are given two tables, customers and orders, and the following (naive) analytics query that computes simple KPIs by customer segment for completed orders between 2025-05-03 and 2025-06-01 (inclusive):
WITH completed_orders AS (
SELECT
o.order_id,
o.customer_id,
o.order_date,
o.amount
FROM orders o
WHERE o.status = 'COMPLETED'
AND o.order_date BETWEEN '2025-05-03' AND '2025-06-01'
),
segment_order_counts AS (
SELECT
c.segment,
COUNT(*) AS orders_count
FROM completed_orders co
JOIN customers c ON c.customer_id = co.customer_id
GROUP BY c.segment
),
segment_revenue AS (
SELECT
c.segment,
SUM(co.amount) AS total_revenue
FROM completed_orders co
JOIN customers c ON c.customer_id = co.customer_id
GROUP BY c.segment
),
segment_customers AS (
SELECT
c.segment,
COUNT(DISTINCT co.customer_id) AS active_customers
FROM completed_orders co
JOIN customers c ON c.customer_id = co.customer_id
GROUP BY c.segment
)
SELECT
soc.segment,
sc.active_customers,
soc.orders_count,
sr.total_revenue
FROM segment_order_counts soc
JOIN segment_revenue sr ON sr.segment = soc.segment
JOIN segment_customers sc ON sc.segment = soc.segment
ORDER BY soc.segment;
This query repeatedly scans the same logical data set (completed_orders) to compute different aggregates.
Task:
1) Refactor this query into a single, optimized SQL statement that produces the same result set but minimizes scans over the orders table by using GROUP BY aggregations (and inline/derived tables if needed).
2) Your output must have one row per customer segment, with columns: segment, active_customers, orders_count, total_revenue, restricted to completed orders between 2025-05-03 and 2025-06-01 (inclusive).
3) In a real interview, you would also briefly explain how you would use EXPLAIN (or EXPLAIN ANALYZE) to verify that the orders table is scanned only once and how you would iterate on the plan, but for this coding task just provide the optimized SQL query.
Tables
customers(customer_id INT, signup_date DATE, segment VARCHAR(20))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2), status VARCHAR(20))
Hints
- You can compute all three aggregates (active_customers, orders_count, total_revenue) in a single GROUP BY over orders joined to customers.
- Filter by status and date range in the WHERE clause before aggregation so the orders table only needs to be scanned once.