Fixing SQL Query Performance With CTEs

performance_cte.md

Fixing SQL Query Performance With CTEs

Original query. Execution time: 127 seconds. +150 million rows.

SELECT
    c.customer_id,
    c.segment,
    SUM(t.amount) AS total_spent
FROM customers c
JOIN transactions t ON c.customer_id = t.customer_id
WHERE t.transaction_date >= '2024-01-01'
GROUP BY c.customer_id, c.segment;

---

Speeding the query up with a CTE

With this queries join was being processed before the transaction_date filter! To fix this, you need to use a CTE. New execution time: 11 seconds.

WITH recent_transactions AS (
    SELECT *
    FROM transactions
    WHERE transaction_date >= '2024-01-01'
)
SELECT
    c.customer_id,
    c.segment,
    SUM(r.amount) AS total_spent
FROM customers c
JOIN recent_transactions r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.segment;

---

Refactored Query, with aggregation in CTE. New execution time <6 seconds.

Now we just add the GROUP BY to the CTE, and then we will be joining the already filtered and grouped data!

WITH recent_spend AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM transactions
    WHERE transaction_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT
    c.customer_id,
    c.segment,
    r.total_spent
FROM customers c
JOIN recent_spend r ON c.customer_id = r.customer_id;

Hell yeah!