In this lesson, you will learn how to optimize your queries by pre-calculating summaries before joining them to your main datasets.
The Problem: The "Heavy Join"
Imagine you have a massive table of orders and you want to see how each order compares to the average order value for that specific customer. If you try to calculate the average on-the-fly while joining to the original table, the database has to perform a heavy calculation for every single row, which slows down your query significantly as the data grows.
The Solution: Pre-calculate and Join
Instead of calculating statistics during the join, we use a "Derived Table" (or a Common Table Expression) to create a summary table first. Think of this as creating a custom "mini-report" that is already finished before the join even starts.
Here is how you would do this to attach an average to every order:
-- Step 1: Pre-calculate the summary
WITH CustomerAverages AS (
SELECT
customer_id,
AVG(order_amount) as avg_spend
FROM orders
GROUP BY customer_id
)
-- Step 2: Join the pre-calculated summary to the main table
SELECT
o.order_id,
o.order_amount,
c.avg_spend
FROM orders o
JOIN CustomerAverages c ON o.customer_id = c.customer_id;
Why This Works
When you group the data first, the database performs the math once for each customer. When the JOIN executes, it simply looks up the pre-calculated avg_spend in your temporary result set.
This approach is much more efficient because the database isn't busy re-calculating the sum or average repeatedly for every row in the orders table. By "shrinking" the dataset down to its unique identifiers and their corresponding summaries before connecting it to your main table, you keep your query operations lean and significantly faster.
Key Takeaway: Always aggregate your data into a summary table first to ensure the database performs calculations once rather than repeating them across every joined row.