slow_query_clinic.sql in SQL

One report written three ways: a correlated subquery, a join, and a window function.

-- The same answer three ways. The plans are the interesting part.

-- 1. Correlated subquery: readable, and it runs once per customer.
EXPLAIN QUERY PLAN
SELECT
    c.name,
    (
        SELECT ROUND(SUM(o.total), 2)
        FROM orders AS o
        WHERE o.customer_id = c.id
            AND o.status = 'paid'
    ) AS revenue
FROM customers AS c
ORDER BY revenue DESC;

-- 2. Aggregate once, then join. One pass over orders.
EXPLAIN QUERY PLAN
WITH totals AS (
    SELECT
        customer_id,
        ROUND(SUM(total), 2) AS revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
)
SELECT
    c.name,
    t.revenue
FROM customers AS c
JOIN totals AS t
    ON t.customer_id = c.id
ORDER BY t.revenue DESC;

-- 3. A window keeps the per-order detail beside the customer total.
EXPLAIN QUERY PLAN
SELECT
    c.name,
    o.id AS order_id,
    o.total,
    ROUND(
        SUM(o.total) OVER (
            PARTITION BY o.customer_id
        ),
        2
    ) AS customer_revenue,
    ROUND(
        o.total * 100.0 / SUM(o.total) OVER (
            PARTITION BY o.customer_id
        ),
        1
    ) AS pct_of_customer
FROM orders AS o
JOIN customers AS c
    ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY c.name, o.placed_at;

How it works

The correlated form re-runs its subquery once per row of the outer query.
The join and group form computes each total once.
The window form gets the total and the row's own detail in one pass.

Keywords and builtins used here

AND
AS
BY
DESC
EXPLAIN
FROM
GROUP
JOIN
ON
ORDER
SELECT
SUM
WHERE
WITH
c

The run, in numbers

Lines: 56
Characters to type: 1061
Tokens: 218
Three-star pace: 100 tpm

At the three-star pace of 100 tokens a minute, this run takes about 131 seconds.

Type this snippet

Step 2 of 2 in Encore, step 17 of 17 in Plans & performance.

← Previous