Writing Multi-Step Analytical Queries: Chaining Subqueries, JOINs, and GROUP BY to Answer Real Business Questions

Introduction

You've been handed a business question: "Which sales reps are closing deals above our average deal size, and what's the revenue breakdown by their region and product category over the last 90 days?" You open your SQL editor, type SELECT, and then — pause. This question can't be answered in one simple clause. It requires you to calculate an average first, then filter against it, then join in rep and region data, then aggregate by multiple dimensions. The pieces are clear, but the order of operations, the structure, the layering — that's where most people get stuck.

This is exactly the gap that separates people who can write SQL from people who think in SQL. Writing multi-step analytical queries isn't about memorizing syntax. It's about developing a mental model for decomposing complex business questions into logical stages, then translating each stage into a query layer that builds cleanly on the one before it. By the time you finish this lesson, you'll have that model. You'll know when to use a subquery versus a CTE versus a derived table, how to chain GROUP BY aggregations without corrupting your joins, and how to structure queries that a colleague — or future you — can actually read and debug.

What you'll learn:

• How to decompose a real business question into sequential analytical steps
• When and how to use subqueries, derived tables, and CTEs as building blocks
• How to chain JOIN and GROUP BY across multiple levels without introducing duplicates or fanout errors
• How to validate intermediate results and catch errors before they propagate
• Performance considerations for choosing the right multi-step structure at scale

---

Prerequisites

This lesson assumes you're already comfortable with the fundamentals. Specifically, you should know:

• How SELECT, FROM, WHERE, and GROUP BY work — if you need a refresher, see SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries
• How JOIN types behave — review SQL JOINs Explained with Real-World Examples if needed
• Basic aggregate functions like COUNT, SUM, AVG — covered in Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG
• What a subquery is and how basic correlated subqueries work — see Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements

---

The Business Scenario: An E-Commerce Analytics Problem

We'll work through a single, realistic scenario from start to finish. Imagine you work at a mid-sized e-commerce company. You have the following tables:

-- Core tables
customers      (customer_id, name, email, signup_date, region)
orders         (order_id, customer_id, order_date, status)
order_items    (item_id, order_id, product_id, quantity, unit_price)
products       (product_id, product_name, category, cost_price)
sales_reps     (rep_id, rep_name, region)
rep_assignments (assignment_id, customer_id, rep_id, assigned_date)

The business question we'll answer: Which sales reps, ranked by total revenue from completed orders in the last 90 days, are exceeding the company-wide average revenue per rep — and what does their per-category breakdown look like?

This requires:

1. Calculating total revenue per order item
2. Filtering to completed orders within 90 days
3. Joining customers to their assigned reps
4. Aggregating revenue by rep
5. Comparing each rep's total to the company average
6. Breaking down revenue by product category per rep

Let's build this step by step.

---

Step 1: Think Before You Type — Decomposing the Question

The most common mistake in complex queries is diving into code before you've mapped the logic. Spend two minutes sketching the stages first. Every analytical question can be broken into:

• Filters (what rows qualify?)
• Joins (what tables need to connect?)
• Aggregations (what computation happens at what grain?)
• Comparisons (does one computed value need to be compared against another computed value?)

For our question:

Stage	Operation	Output
1	Filter orders: status = 'completed', last 90 days	Qualifying orders
2	Calculate revenue per item (qty × price)	Item-level revenue
3	Join to customers → rep_assignments → sales_reps	Each order linked to a rep
4	Aggregate by rep: total revenue	Rep-level totals
5	Calculate company-wide average revenue per rep	Scalar value
6	Filter reps above average	Above-average reps
7	Break down by rep + category	Final result

This map tells you something important: Stage 5 must happen before Stage 6, and Stage 6 must reference both Stage 4 and Stage 5. That means you can't do this in a single pass. You need multiple layers.

---

Step 2: Build the Foundation — Revenue per Qualifying Order Item

Always start from the most granular level: the row-level calculation. Here we're computing revenue at the order_items level, filtered to qualifying orders.

-- Step 2: Item-level revenue for qualifying orders
SELECT
    oi.order_id,
    oi.product_id,
    p.category,
    oi.quantity * oi.unit_price AS item_revenue
FROM order_items oi
JOIN orders o
    ON oi.order_id = o.order_id
JOIN products p
    ON oi.product_id = p.product_id
WHERE
    o.status = 'completed'
    AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'

Run this first. Check the row count. Does it make sense? If you expect tens of thousands of items and you're seeing 12 rows, something's wrong with your filter or your join.

This intermediate result has one row per order item. Notice we're not aggregating yet — we're just computing the item_revenue field and pulling in the category label we'll need later.

---

Step 3: Link Orders to Sales Reps via the Customer Chain

Now we need to know which rep "owns" each order. The path is: orders → customers → rep_assignments → sales_reps. This is a multi-hop join, and it's where people often introduce duplicates.

-- Step 3: Order items linked to their rep
SELECT
    oi.order_id,
    oi.product_id,
    p.category,
    oi.quantity * oi.unit_price AS item_revenue,
    sr.rep_id,
    sr.rep_name,
    sr.region
FROM order_items oi
JOIN orders o
    ON oi.order_id = o.order_id
JOIN products p
    ON oi.product_id = p.product_id
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN rep_assignments ra
    ON c.customer_id = ra.customer_id
JOIN sales_reps sr
    ON ra.rep_id = sr.rep_id
WHERE
    o.status = 'completed'
    AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'

Stop here and check for duplicates. A common trap: if rep_assignments can have multiple active records per customer (e.g., a customer was reassigned over time), every order item will fan out to multiple rows — one per assignment. You can spot this by running:

SELECT COUNT(*), COUNT(DISTINCT order_id || '-' || product_id)
FROM (-- paste the above query here)

If those two numbers differ, you have fanout. The fix depends on your business logic. If you want the current rep, filter rep_assignments to the most recent active record:

JOIN rep_assignments ra
    ON c.customer_id = ra.customer_id
    AND ra.assignment_id = (
        SELECT MAX(ra2.assignment_id)
        FROM rep_assignments ra2
        WHERE ra2.customer_id = c.customer_id
    )

This correlated subquery inside a JOIN condition is a clean, readable pattern for "give me the latest record per group."

---

Step 4: First Aggregation — Revenue by Rep and Category

Now we aggregate the item-level result up to the rep + category grain. This is where GROUP BY earns its keep.

-- Step 4: Revenue by rep and category
SELECT
    sr.rep_id,
    sr.rep_name,
    sr.region,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS category_revenue
FROM order_items oi
JOIN orders o
    ON oi.order_id = o.order_id
JOIN products p
    ON oi.product_id = p.product_id
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN rep_assignments ra
    ON c.customer_id = ra.customer_id
    AND ra.assignment_id = (
        SELECT MAX(ra2.assignment_id)
        FROM rep_assignments ra2
        WHERE ra2.customer_id = c.customer_id
    )
JOIN sales_reps sr
    ON ra.rep_id = sr.rep_id
WHERE
    o.status = 'completed'
    AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY
    sr.rep_id,
    sr.rep_name,
    sr.region,
    p.category
ORDER BY
    sr.rep_name,
    category_revenue DESC

This query produces one row per rep-category combination. A rep who sold Electronics, Apparel, and Home Goods will have three rows. Notice that rep_name and region are included in GROUP BY even though they're functionally dependent on rep_id — most SQL dialects require this when you SELECT non-aggregated columns.

At this point, check a spot: pick a rep you know had significant activity and verify their category totals manually or against another report. This is the last place where errors are easy to isolate.

---

Step 5: Second Aggregation — Total Revenue per Rep

To compare each rep against the company average, we need a total per rep (collapsing categories), and then a company-wide average of those totals.

This is the "two aggregations at different grains" problem. You cannot do this in one GROUP BY — you need to aggregate twice. The clean solution is to use the Step 4 result as a subquery or CTE, then aggregate again.

-- Step 5: Total revenue per rep (wrapping Step 4 as a CTE)
WITH rep_category_revenue AS (
    SELECT
        sr.rep_id,
        sr.rep_name,
        sr.region,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS category_revenue
    FROM order_items oi
    JOIN orders o
        ON oi.order_id = o.order_id
    JOIN products p
        ON oi.product_id = p.product_id
    JOIN customers c
        ON o.customer_id = c.customer_id
    JOIN rep_assignments ra
        ON c.customer_id = ra.customer_id
        AND ra.assignment_id = (
            SELECT MAX(ra2.assignment_id)
            FROM rep_assignments ra2
            WHERE ra2.customer_id = c.customer_id
        )
    JOIN sales_reps sr
        ON ra.rep_id = sr.rep_id
    WHERE
        o.status = 'completed'
        AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY
        sr.rep_id,
        sr.rep_name,
        sr.region,
        p.category
),

rep_total_revenue AS (
    SELECT
        rep_id,
        rep_name,
        region,
        SUM(category_revenue) AS total_revenue
    FROM rep_category_revenue
    GROUP BY
        rep_id,
        rep_name,
        region
)

SELECT * FROM rep_total_revenue
ORDER BY total_revenue DESC

We now have a clean CTE chain: rep_category_revenue is the base grain, rep_total_revenue collapses it to one row per rep. Each CTE is a named, reusable intermediate result.

For deeper coverage of CTE patterns and when to prefer them over subqueries, see Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture.

---

Step 6: Computing the Company Average and Filtering

Here's where the real multi-step power comes in. We need to compute the average of total_revenue across all reps, then keep only those above it. We'll add a third CTE.

-- Step 6: Add the company average and filter
WITH rep_category_revenue AS (
    -- (same as Step 5)
    SELECT
        sr.rep_id,
        sr.rep_name,
        sr.region,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS category_revenue
    FROM order_items oi
    JOIN orders o
        ON oi.order_id = o.order_id
    JOIN products p
        ON oi.product_id = p.product_id
    JOIN customers c
        ON o.customer_id = c.customer_id
    JOIN rep_assignments ra
        ON c.customer_id = ra.customer_id
        AND ra.assignment_id = (
            SELECT MAX(ra2.assignment_id)
            FROM rep_assignments ra2
            WHERE ra2.customer_id = c.customer_id
        )
    JOIN sales_reps sr
        ON ra.rep_id = sr.rep_id
    WHERE
        o.status = 'completed'
        AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY
        sr.rep_id,
        sr.rep_name,
        sr.region,
        p.category
),

rep_total_revenue AS (
    SELECT
        rep_id,
        rep_name,
        region,
        SUM(category_revenue) AS total_revenue
    FROM rep_category_revenue
    GROUP BY
        rep_id,
        rep_name,
        region
),

company_average AS (
    SELECT
        AVG(total_revenue) AS avg_rep_revenue
    FROM rep_total_revenue
)

SELECT
    rtr.rep_id,
    rtr.rep_name,
    rtr.region,
    rtr.total_revenue,
    ca.avg_rep_revenue,
    ROUND(rtr.total_revenue - ca.avg_rep_revenue, 2) AS above_average_by
FROM rep_total_revenue rtr
CROSS JOIN company_average ca
WHERE rtr.total_revenue > ca.avg_rep_revenue
ORDER BY rtr.total_revenue DESC

The CROSS JOIN company_average is intentional and correct here. company_average returns exactly one row (a single AVG() scalar), so cross-joining it attaches that value to every rep row without duplication. This is the idiomatic SQL pattern for broadcasting a scalar result across a dataset.

Note the above_average_by column — we're adding business context, not just a boolean filter. A report that says "rep is above average" is less useful than "rep is $47,200 above average."

---

Step 7: The Final Query — Adding the Category Breakdown

Now we bring back the category breakdown for only those above-average reps. We join the filtered rep list back to rep_category_revenue.

-- Step 7: Complete query with category breakdown
WITH rep_category_revenue AS (
    SELECT
        sr.rep_id,
        sr.rep_name,
        sr.region,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS category_revenue
    FROM order_items oi
    JOIN orders o
        ON oi.order_id = o.order_id
    JOIN products p
        ON oi.product_id = p.product_id
    JOIN customers c
        ON o.customer_id = c.customer_id
    JOIN rep_assignments ra
        ON c.customer_id = ra.customer_id
        AND ra.assignment_id = (
            SELECT MAX(ra2.assignment_id)
            FROM rep_assignments ra2
            WHERE ra2.customer_id = c.customer_id
        )
    JOIN sales_reps sr
        ON ra.rep_id = sr.rep_id
    WHERE
        o.status = 'completed'
        AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY
        sr.rep_id,
        sr.rep_name,
        sr.region,
        p.category
),

rep_total_revenue AS (
    SELECT
        rep_id,
        rep_name,
        region,
        SUM(category_revenue) AS total_revenue
    FROM rep_category_revenue
    GROUP BY
        rep_id,
        rep_name,
        region
),

company_average AS (
    SELECT
        AVG(total_revenue) AS avg_rep_revenue
    FROM rep_total_revenue
),

above_average_reps AS (
    SELECT
        rtr.rep_id,
        rtr.rep_name,
        rtr.region,
        rtr.total_revenue,
        ca.avg_rep_revenue,
        ROUND(rtr.total_revenue - ca.avg_rep_revenue, 2) AS above_average_by
    FROM rep_total_revenue rtr
    CROSS JOIN company_average ca
    WHERE rtr.total_revenue > ca.avg_rep_revenue
)

SELECT
    aar.rep_name,
    aar.region,
    aar.total_revenue,
    aar.avg_rep_revenue,
    aar.above_average_by,
    rcr.category,
    rcr.category_revenue,
    ROUND(rcr.category_revenue / aar.total_revenue * 100, 1) AS pct_of_rep_total
FROM above_average_reps aar
JOIN rep_category_revenue rcr
    ON aar.rep_id = rcr.rep_id
ORDER BY
    aar.total_revenue DESC,
    rcr.category_revenue DESC

The final pct_of_rep_total column adds another layer of analytical value: for each qualifying rep, you can see not just how much they made per category, but what share of their portfolio it represents.

This query answers the original business question completely. It took seven logical steps, but each CTE is clean, named, and testable independently.

---

Choosing Your Structure: CTEs vs. Subqueries vs. Derived Tables

We used CTEs throughout the example above, but that's not always the right choice. Let's be precise about when each pattern fits.

Common Table Expressions (CTEs)

Use CTEs when:

• You need to reference the same intermediate result more than once
• The query has 3+ logical stages and readability matters
• You're working in a codebase where colleagues will maintain the query
• You want to test each stage independently by temporarily adding SELECT * FROM that_cte

Trade-offs: Most modern databases (PostgreSQL, SQL Server, BigQuery, Snowflake) optimize CTEs intelligently. However, MySQL pre-8.0 didn't support CTEs at all, and in some older versions of SQL Server, CTEs were always materialized (computed once and cached), which could hurt or help performance depending on context.

Subqueries (Inline)

SELECT *
FROM (
    SELECT rep_id, SUM(revenue) AS total_revenue
    FROM transactions
    GROUP BY rep_id
) rep_totals
WHERE total_revenue > 50000

Use subqueries when:

• The logic is used exactly once and is short enough to read inline
• You're writing a quick ad hoc query and don't need reusability
• The outer query's WHERE clause needs to reference a scalar computed from the data (WHERE salary > (SELECT AVG(salary) FROM employees))

Avoid subqueries when:

• They're deeply nested (3+ levels of nesting destroys readability)
• The same subquery appears twice — that's technical debt waiting to happen

Derived Tables

A derived table is essentially a subquery in the FROM clause. It's what we call a subquery when it's unnamed (or named only with an alias). The CTE pattern emerged specifically to give these a reusable name and pull them out of the FROM clause clutter. For a full treatment of derived tables and inline views, see Writing SQL FROM Scratch: Structuring Multi-Step Analytical Queries with Derived Tables and Inline Views.

---

Validating Intermediate Results: The Debug Loop

One of the most valuable skills in multi-step query writing is systematic validation. Here's the debugging loop you should internalize:

1. Check row counts at each layer

-- After Step 4, check that row count makes sense
SELECT COUNT(*) FROM rep_category_revenue;
-- Also check: how many distinct reps?
SELECT COUNT(DISTINCT rep_id) FROM rep_category_revenue;

2. Spot-check a known entity

Pick a rep, customer, or product you can verify against another source (a Salesforce report, a spreadsheet, your own knowledge). Query only their rows:

SELECT * FROM rep_category_revenue
WHERE rep_name = 'Jordan Kim';

3. Sanity-check aggregates

Your total revenue summed across all reps should equal total revenue calculated directly from order_items:

-- Direct calculation
SELECT SUM(oi.quantity * oi.unit_price)
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
AND o.order_date >= CURRENT_DATE - INTERVAL '90 days';

-- vs. sum of rep totals
SELECT SUM(total_revenue) FROM rep_total_revenue;

If these don't match, there's either a join creating duplication or an order that isn't assigned to any rep (it would be excluded from the rep join). Neither is wrong in isolation — but you need to know which is happening.

4. Test the scalar CTE

SELECT * FROM company_average;
-- Should return exactly one row. If it returns more, something's wrong.

---

Performance Considerations at Scale

Writing correct multi-step queries is necessary; writing performant ones is what separates good analysts from great ones. Here's where you need to think carefully.

The Correlated Subquery Tax

In Step 3, we used a correlated subquery to find the most recent rep_assignment per customer:

AND ra.assignment_id = (
    SELECT MAX(ra2.assignment_id)
    FROM rep_assignments ra2
    WHERE ra2.customer_id = c.customer_id
)

This runs once per row in the join. If you have 100,000 customers, that's 100,000 separate subquery executions. At scale, this hurts. The alternative is to pre-deduplicate rep_assignments in its own CTE using ROW_NUMBER():

WITH latest_assignments AS (
    SELECT
        customer_id,
        rep_id,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY assignment_id DESC
        ) AS rn
    FROM rep_assignments
),

deduplicated_assignments AS (
    SELECT customer_id, rep_id
    FROM latest_assignments
    WHERE rn = 1
)

Now deduplicated_assignments is a clean one-row-per-customer table you join to cleanly, and the optimizer can handle it with a proper hash join or merge join instead of repeated lookups.

For a complete treatment of window functions like ROW_NUMBER(), see Window Functions: RANK, ROW_NUMBER, and LAG.

Pushing Filters Down

Your query optimizer will generally push WHERE filters as early as possible, but complex CTEs can sometimes prevent this. To ensure filters apply before joins:

-- More efficient: filter orders early in its own CTE
WITH qualifying_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE status = 'completed'
    AND order_date >= CURRENT_DATE - INTERVAL '90 days'
),

qualifying_items AS (
    SELECT
        oi.order_id,
        oi.product_id,
        oi.quantity * oi.unit_price AS item_revenue,
        qo.customer_id
    FROM order_items oi
    JOIN qualifying_orders qo ON oi.order_id = qo.order_id
)

By materializing qualifying_orders first, you ensure you're only joining the subset of orders that matter — not the full orders table followed by a late filter.

Index Awareness

The joins in this query rely on:

• orders.customer_id (for joining to customers)
• order_items.order_id (for joining to orders)
• rep_assignments.customer_id (for joining to customers)
• orders.order_date and orders.status (for filtering)

If these columns aren't indexed, you'll see sequential scans on potentially millions of rows. Before you optimize the query structure, check whether these indexes exist:

-- PostgreSQL
\d orders
-- SQL Server
EXEC sp_helpindex 'orders'

For a deep dive on index strategy, see SQL Indexes Explained: How They Work and When to Create Them.

---

Alternative Patterns: When the CTE Chain Isn't Enough

Sometimes a CTE chain is the right tool; sometimes you need a different pattern entirely.

Window Functions as a Substitute for Double Aggregation

Instead of aggregating to rep totals and then computing an average in a separate CTE, you can sometimes use window functions to compute the company average while preserving individual rows:

SELECT
    rep_id,
    rep_name,
    region,
    total_revenue,
    AVG(total_revenue) OVER () AS company_avg_revenue,
    total_revenue - AVG(total_revenue) OVER () AS above_average_by
FROM rep_total_revenue
WHERE total_revenue > AVG(total_revenue) OVER ()

Wait — that WHERE clause won't work. WHERE is evaluated before window functions are computed. You'd need to wrap it:

SELECT *
FROM (
    SELECT
        rep_id,
        rep_name,
        region,
        total_revenue,
        AVG(total_revenue) OVER () AS company_avg_revenue
    FROM rep_total_revenue
) windowed
WHERE total_revenue > company_avg_revenue

This is more compact, but harder to read for analysts who aren't window function fluent. It's a trade-off between elegance and accessibility.

HAVING for Single-Level Filters

If you didn't need the company average (say, you just wanted reps with total revenue above a fixed threshold of $100,000), you could use HAVING instead of a subquery:

SELECT
    sr.rep_id,
    sr.rep_name,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
-- ... joins ...
GROUP BY sr.rep_id, sr.rep_name
HAVING SUM(oi.quantity * oi.unit_price) > 100000

HAVING filters after aggregation, making it appropriate for aggregate conditions. It can't reference window functions, and it can't compare one aggregate value to another aggregate computed across a different grouping. Once you need that cross-group comparison (as we did), you're back to CTEs or subqueries.

For more depth on HAVING patterns, see Ranking and Filtering Groups with HAVING: Writing Conditional Aggregates That Go Beyond WHERE.

---

Applying Conditional Logic Inside Aggregations

One powerful extension of the multi-step pattern: using CASE WHEN inside aggregate functions to compute conditional subtotals without additional query layers.

Suppose you want to know, for each above-average rep, what share of their revenue came from high-margin vs. low-margin products (defined as whether cost_price is less than 40% of unit_price):

SELECT
    sr.rep_id,
    sr.rep_name,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    SUM(
        CASE
            WHEN p.cost_price < oi.unit_price * 0.4
            THEN oi.quantity * oi.unit_price
            ELSE 0
        END
    ) AS high_margin_revenue,
    SUM(
        CASE
            WHEN p.cost_price >= oi.unit_price * 0.4
            THEN oi.quantity * oi.unit_price
            ELSE 0
        END
    ) AS low_margin_revenue
FROM order_items oi
-- ... joins ...
GROUP BY sr.rep_id, sr.rep_name

This computes three aggregates in a single pass — total, high-margin subset, and low-margin subset — without joining the table three times. For more patterns like this, see Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice.

---

Hands-On Exercise

Use the schema from this lesson (or adapt to your own data). Complete the following:

Exercise 1 — Intermediate: Write a query that returns the top 3 product categories by total revenue for each region. Your query must use at least one CTE and one level of subquery or window function to handle the "top 3 per group" ranking.

Hints:

• Start by computing revenue per region + category in a CTE
• Use ROW_NUMBER() OVER (PARTITION BY region ORDER BY category_revenue DESC) to rank within each region
• Filter the outer query for rn <= 3

Exercise 2 — Advanced: Extend the main lesson query to also show, for each above-average rep, the number of unique customers they served and their average order value. Make sure you don't double-count customers who placed multiple orders.

Hints:

• Add COUNT(DISTINCT o.customer_id) and SUM(...) / COUNT(DISTINCT o.order_id) in the base CTE
• Carry those values through your aggregation chain
• Verify: SUM(category_revenue) in the final query should equal total_revenue from rep_total_revenue

Exercise 3 — Expert: Rewrite the correlated subquery in the rep_assignments join (used to find the most recent assignment) as a CTE using ROW_NUMBER(). Verify that the final query produces identical results. Then run EXPLAIN ANALYZE (PostgreSQL) or look at the estimated cost in your query tool of choice. Which version reads fewer rows?

---

Common Mistakes & Troubleshooting

Mistake 1: Aggregating Before Joining (or Joining Before Filtering)

Joining a full multi-million-row table and then filtering is expensive. Filtering inside a CTE or subquery first, then joining the smaller result set, is almost always faster. The optimizer usually handles this, but not always — especially with complex CTEs.

Mistake 2: Grouping by the Wrong Grain

If you group by rep_id, rep_name, region, category but then try to JOIN back on just rep_id, you'll get multiple rows per rep in the join result — which will silently inflate your totals again. Always be explicit about the granularity of each CTE and what key you'll join on.

Mistake 3: Forgetting That NULL Propagates in Aggregations

If any unit_price or quantity is NULL, the product quantity * unit_price is NULL. SUM() ignores NULLs, so your totals will silently exclude those rows. Use COALESCE(unit_price, 0) or investigate why nulls exist.

COALESCE(oi.quantity, 0) * COALESCE(oi.unit_price, 0) AS item_revenue

Mistake 4: Referencing a CTE Before It's Defined

CTEs are defined in order. You cannot reference rep_total_revenue in the company_average CTE if rep_total_revenue is defined after it. Read your CTE block top-to-bottom — dependencies must flow downward.

Mistake 5: Using DISTINCT as a Fanout Band-Aid

If you find yourself writing SELECT DISTINCT * to "fix" duplicate rows after a join, stop. DISTINCT hides fanout; it doesn't fix it. Figure out why the join is producing duplicates and address the root cause. Otherwise, you'll get the right number of distinct rows but wrong aggregate values because the duplicates inflated your SUM before you deduplicated.

Mistake 6: Overcomplicating When HAVING Is Enough

If your filter is on a simple aggregate threshold (not cross-group comparison), use HAVING. Wrapping a simple GROUP BY / HAVING query in two CTEs when HAVING SUM(...) > 10000 would work is unnecessary complexity. Know when the simpler tool is the right tool.

---

Summary & Next Steps

You started with a business question that felt like it required six different reports to answer. You ended up with a single, coherent SQL query that answers it completely, correctly, and in a way that another analyst can read, debug, and extend.

The core principles to carry forward:

• Decompose first, code second. Map your analytical stages before writing a line of SQL.
• Build incrementally. Each CTE should be runnable and verifiable on its own.
• Control your grain. Know what level of granularity each layer produces and what key you'll join on.
• Validate at every step. Row counts, spot checks, and aggregate reconciliation catch bugs before they become incorrect dashboards.
• Choose structure deliberately. CTEs for multi-use and readability; subqueries for one-off scalar comparisons; HAVING for aggregate filters within a single group; window functions when you need both row-level detail and aggregate context.

Where to go next:

• To push this pattern into real-world cohort and funnel analysis, see SQL for Data Analysis: Cohort Analysis, Funnels, and Retention - Complete Guide
• To master the window function patterns referenced in Step 5, work through Window Functions: RANK, ROW_NUMBER, and LAG
• To understand how to make these queries fast at production scale, read Database Performance Tuning: Advanced Indexing Strategies and Query Rewriting for Production Systems
• For recursive and hierarchical CTE patterns (the next evolution of this skill), see Advanced CTEs: Recursive Queries and Hierarchical Data

The ability to write multi-step analytical queries is the inflection point between being a SQL user and being a SQL thinker. You've crossed it.