Optimize SQL to minimize scans

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.

Refactor Analytics Query to Minimize Table Scans

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.

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY
signup_date	DATE	NOT NULL
segment	VARCHAR(20)	NOT NULL

Sample data:

customer_id	segment	signup_date
1	enterprise	2025-01-10
2	small_business	2025-02-05
3	enterprise	2025-03-12
4	individual	2025-05-20
5	small_business	2025-04-01

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

amount	customer_id	order_date	order_id	status
100	1	2025-04-15	1	COMPLETED
200	1	2025-05-05	2	COMPLETED
150	1	2025-05-20	3	REFUNDED
80	2	2025-05-10	4	COMPLETED
120	2	2025-05-15	5	COMPLETED
50	2	2025-06-01	6	PENDING
300	3	2025-05-25	7	COMPLETED
400	3	2025-06-01	8	COMPLETED
60	4	2025-05-21	9	COMPLETED
40	5	2025-03-10	10	COMPLETED
70	5	2025-05-03	11	COMPLETED
90	5	2025-06-02	12	COMPLETED

Expected output

One row per customer segment summarizing completed orders between 2025-05-03 and 2025-06-01 (inclusive). For each segment, return the number of distinct customers with at least one completed order in that window (active_customers), the total number of such orders (orders_count), and the total revenue from those orders (total_revenue).

active_customers	orders_count	segment	total_revenue
2	3	enterprise	900
1	1	individual	60
2	3	small_business	270

Hints

1. You can compute all three aggregates (active_customers, orders_count, total_revenue) in a single GROUP BY over orders joined to customers.
2. Filter by status and date range in the WHERE clause before aggregation so the orders table only needs to be scanned once.