Building a Complete Analytical Query from Scratch: Combining SELECT, JOIN, GROUP BY, and Subqueries to Answer a Multi-Part Business Question

Introduction

You've been handed a business question that sounds deceptively simple: "Which of our sales representatives are underperforming relative to their regional average, and what product categories are dragging them down?" On the surface, it feels like a reporting problem. In practice, it's a multi-dimensional analytical problem that requires you to pull data from several tables, aggregate it at different levels of granularity, compare individual results against group benchmarks, and filter based on computed values — all in a single coherent query.

Most SQL learners get comfortable with individual clauses in isolation. They can write a GROUP BY that summarizes revenue. They can write a JOIN that connects orders to customers. But when a real business question arrives — one that needs all of those tools working together — they freeze, or they write three separate queries and paste the results into a spreadsheet. This lesson exists to break that pattern. By the end, you'll be able to take a complex, multi-part business question, decompose it into logical layers, and build a single well-structured SQL query that answers all of it.

What you'll learn:

• How to decompose a multi-part business question into a layered SQL strategy before writing a single line of code
• How to combine JOIN, GROUP BY, and aggregation to produce a joined-and-summarized result set across multiple tables
• How to use subqueries and derived tables as building blocks inside a larger query
• How to compare individual rows against aggregate benchmarks (a pattern that appears constantly in real analytics)
• How to use HAVING, CASE WHEN, and column aliases to make your query both analytically correct and readable

---

Prerequisites

This lesson targets professionals who already have working SQL knowledge. You should be comfortable with:

• Writing SELECT statements with WHERE and ORDER BY — if you need a refresher, the lesson on SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries covers that foundation
• Basic JOIN syntax — the lesson on SQL JOINs Explained with Real-World Examples is the right prerequisite reference
• The mechanics of GROUP BY and aggregate functions — covered in Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG
• A basic familiarity with subqueries — you'll go much deeper here, but Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements is a good warm-up

We'll be using PostgreSQL syntax throughout, but the concepts apply equally well to MySQL, SQL Server, and BigQuery with minor syntax variations.

---

The Business Problem and the Schema

Let's establish the scenario in full before touching SQL. You work as a data analyst at a mid-sized B2B software and hardware distributor. The business has regional sales reps who manage accounts and close orders. Leadership wants to understand who is underperforming within their own region, and specifically which product categories are contributing to that underperformance — because the intervention strategy (coaching vs. product reassignment vs. territory restructuring) depends on the category pattern.

The specific question from leadership is:

"Show me every sales rep whose total revenue for Q1 2024 was below their regional average. For each of those reps, break down their revenue by product category and flag which categories are below the regional category average."

This is genuinely a two-part question with nested comparison logic. Part one requires comparing rep-level revenue to a regional aggregate. Part two requires a category-level comparison that is also regional. We'll need to solve both.

Here's the schema we're working with:

-- Sales representatives
CREATE TABLE sales_reps (
    rep_id       INT PRIMARY KEY,
    rep_name     VARCHAR(100),
    region       VARCHAR(50)
);

-- Customers (accounts)
CREATE TABLE customers (
    customer_id  INT PRIMARY KEY,
    company_name VARCHAR(150),
    rep_id       INT REFERENCES sales_reps(rep_id)
);

-- Orders placed by customers
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT REFERENCES customers(customer_id),
    order_date   DATE,
    status       VARCHAR(30)   -- 'completed', 'cancelled', 'pending'
);

-- Line items within an order
CREATE TABLE order_items (
    item_id      INT PRIMARY KEY,
    order_id     INT REFERENCES orders(order_id),
    product_id   INT REFERENCES products(product_id),
    quantity     INT,
    unit_price   NUMERIC(10,2)
);

-- Products
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(150),
    category     VARCHAR(80)
);

Five tables. The revenue lives in order_items (as quantity * unit_price). The rep lives in sales_reps. To connect them, you have to traverse: order_items → orders → customers → sales_reps. Products connect via order_items → products.

---

Step 1: Decompose the Question Before Writing SQL

Resist the urge to open a query editor and start typing. The most expensive mistake in analytical SQL is writing code before you understand the shape of the answer. Spend five minutes decomposing the question into layers.

What we need to produce, ultimately:

rep_name	region	rep_q1_revenue	regional_avg_revenue	category	rep_category_revenue	regional_category_avg	below_category_avg
Jordan Mills	Northeast	87,400	112,000	Hardware	24,000	38,000	Yes
Jordan Mills	Northeast	87,400	112,000	Software	63,400	74,000	Yes

This output tells us immediately what building blocks we need:

1. Rep-level Q1 revenue — aggregate order_items filtered to Q1 2024, joined through orders → customers → sales_reps, grouped by rep
2. Regional average revenue — aggregate the rep-level revenue further, grouped by region
3. Filter to underperformers — compare #1 to #2, keep only reps below average
4. Category-level revenue per rep — the same join chain but now also grouped by products.category
5. Regional category average — aggregate #4 further, grouped by region and category
6. Flag categories below regional category average — compare #4 to #5

Notice that items 2 and 5 are both aggregates of aggregates. You can't get a regional average of rep revenue in a single flat GROUP BY — you need to group by rep first, then average those grouped results. This is exactly the kind of problem where subqueries (or CTEs) are the correct tool.

---

Step 2: Build the Foundation — Rep-Level Revenue

Start with the innermost, most fundamental piece: how much revenue did each rep generate in Q1 2024, from completed orders only?

SELECT
    sr.rep_id,
    sr.rep_name,
    sr.region,
    SUM(oi.quantity * oi.unit_price) AS rep_q1_revenue
FROM sales_reps sr
JOIN customers c
    ON c.rep_id = sr.rep_id
JOIN orders o
    ON o.customer_id = c.customer_id
JOIN order_items oi
    ON oi.order_id = o.order_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_date <  '2024-04-01'
  AND o.status = 'completed'
GROUP BY
    sr.rep_id,
    sr.rep_name,
    sr.region;

Run this query on its own and look at the results before you proceed. A few things to verify:

• Are the row counts reasonable? If you have 24 sales reps, you should see at most 24 rows (fewer if some had zero completed orders in Q1).
• Do the revenue figures pass a sanity check against known benchmarks or previous reports?
• Are there any NULL revenues? That would indicate a rep exists but had no matching order items — worth understanding.

This query is your foundation. Every subsequent layer builds on top of it — we're going to wrap it in subqueries and derive new columns from it. For that reason, getting it correct now is critical.

---

Step 3: Add the Regional Average as a Subquery

Now we need to compare each rep's revenue to their regional average. The challenge is that the regional average is derived from the same rep-level revenue we just computed — it's an aggregate of that aggregate.

The approach: wrap the previous query as a subquery (a derived table), then compute the regional average over that derived table, then join the two together.

-- Step 3: Compare rep revenue to regional average
SELECT
    rep_data.rep_id,
    rep_data.rep_name,
    rep_data.region,
    rep_data.rep_q1_revenue,
    regional_avg.avg_regional_revenue
FROM (
    -- Subquery A: rep-level Q1 revenue
    SELECT
        sr.rep_id,
        sr.rep_name,
        sr.region,
        SUM(oi.quantity * oi.unit_price) AS rep_q1_revenue
    FROM sales_reps sr
    JOIN customers c    ON c.rep_id = sr.rep_id
    JOIN orders o       ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_date <  '2024-04-01'
      AND o.status = 'completed'
    GROUP BY sr.rep_id, sr.rep_name, sr.region
) AS rep_data
JOIN (
    -- Subquery B: regional average of rep revenues
    SELECT
        region,
        AVG(rep_q1_revenue) AS avg_regional_revenue
    FROM (
        SELECT
            sr.rep_id,
            sr.region,
            SUM(oi.quantity * oi.unit_price) AS rep_q1_revenue
        FROM sales_reps sr
        JOIN customers c    ON c.rep_id = sr.rep_id
        JOIN orders o       ON o.customer_id = c.customer_id
        JOIN order_items oi ON oi.order_id = o.order_id
        WHERE o.order_date >= '2024-01-01'
          AND o.order_date <  '2024-04-01'
          AND o.status = 'completed'
        GROUP BY sr.rep_id, sr.region
    ) AS regional_rep_data
    GROUP BY region
) AS regional_avg
ON regional_avg.region = rep_data.region
WHERE rep_data.rep_q1_revenue < regional_avg.avg_regional_revenue
ORDER BY rep_data.region, rep_data.rep_q1_revenue;

Let's be honest: this is getting verbose, and we're duplicating the core join chain twice. That's a sign it's time to introduce CTEs to clean this up. But let's understand what's happening before we refactor.

Subquery A produces one row per rep with their Q1 revenue. Subquery B averages those revenues within each region, producing one row per region. We then JOIN the two on region, which attaches the regional benchmark to every rep row. The WHERE clause then filters to only the reps below that benchmark.

---

Step 4: Refactor with CTEs for Readability and Maintainability

The duplicated join chain in Step 3 is a maintenance nightmare. If the date range changes, you have to update it in two places. If a status filter changes, same problem. CTEs (Common Table Expressions) solve this cleanly by defining a named result set once and referencing it multiple times.

WITH

-- CTE 1: Core rep-level revenue for Q1 2024
rep_revenue AS (
    SELECT
        sr.rep_id,
        sr.rep_name,
        sr.region,
        SUM(oi.quantity * oi.unit_price) AS rep_q1_revenue
    FROM sales_reps sr
    JOIN customers c    ON c.rep_id = sr.rep_id
    JOIN orders o       ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_date <  '2024-04-01'
      AND o.status = 'completed'
    GROUP BY sr.rep_id, sr.rep_name, sr.region
),

-- CTE 2: Regional averages derived from CTE 1
regional_averages AS (
    SELECT
        region,
        AVG(rep_q1_revenue) AS avg_regional_revenue,
        COUNT(rep_id)       AS rep_count_in_region
    FROM rep_revenue
    GROUP BY region
),

-- CTE 3: Underperforming reps only
underperformers AS (
    SELECT
        rr.rep_id,
        rr.rep_name,
        rr.region,
        rr.rep_q1_revenue,
        ra.avg_regional_revenue,
        ra.rep_count_in_region,
        ROUND(rr.rep_q1_revenue / ra.avg_regional_revenue * 100, 1) AS pct_of_regional_avg
    FROM rep_revenue rr
    JOIN regional_averages ra ON ra.region = rr.region
    WHERE rr.rep_q1_revenue < ra.avg_regional_revenue
)

SELECT * FROM underperformers
ORDER BY region, rep_q1_revenue;

This is much better. Notice a few details:

• CTE 2 references CTE 1 directly — no need to re-run the join chain. This is one of the most powerful features of CTEs.
• We added rep_count_in_region because a "regional average" of 2 reps has very different analytical weight than one computed from 12 reps. Including it gives the business analyst context they'll need.
• We computed pct_of_regional_avg — a derived metric that's more interpretable than raw dollar gaps for cross-region comparisons.

The lesson on Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture goes deep on the performance characteristics of CTEs versus subqueries — it's worth reading once you're comfortable with the structural patterns.

---

Step 5: Add the Category-Level Breakdown

Now we address part two of the business question: for each underperforming rep, break down their revenue by product category and flag categories where they're below the regional category average.

This requires two more CTEs: one for rep-category level revenue, and one for regional-category level averages.

WITH

-- CTE 1: Core rep-level revenue for Q1 2024
rep_revenue AS (
    SELECT
        sr.rep_id,
        sr.rep_name,
        sr.region,
        SUM(oi.quantity * oi.unit_price) AS rep_q1_revenue
    FROM sales_reps sr
    JOIN customers c    ON c.rep_id = sr.rep_id
    JOIN orders o       ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_date <  '2024-04-01'
      AND o.status = 'completed'
    GROUP BY sr.rep_id, sr.rep_name, sr.region
),

-- CTE 2: Regional averages
regional_averages AS (
    SELECT
        region,
        AVG(rep_q1_revenue) AS avg_regional_revenue,
        COUNT(rep_id)       AS rep_count_in_region
    FROM rep_revenue
    GROUP BY region
),

-- CTE 3: Underperforming reps
underperformers AS (
    SELECT
        rr.rep_id,
        rr.rep_name,
        rr.region,
        rr.rep_q1_revenue,
        ra.avg_regional_revenue,
        ROUND(rr.rep_q1_revenue / ra.avg_regional_revenue * 100, 1) AS pct_of_regional_avg
    FROM rep_revenue rr
    JOIN regional_averages ra ON ra.region = rr.region
    WHERE rr.rep_q1_revenue < ra.avg_regional_revenue
),

-- CTE 4: Category revenue per rep (all reps, filtered to Q1 completed orders)
rep_category_revenue AS (
    SELECT
        sr.rep_id,
        sr.region,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS category_revenue
    FROM sales_reps sr
    JOIN customers c    ON c.rep_id = sr.rep_id
    JOIN orders o       ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    JOIN products p     ON p.product_id = oi.product_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_date <  '2024-04-01'
      AND o.status = 'completed'
    GROUP BY sr.rep_id, sr.region, p.category
),

-- CTE 5: Regional average revenue per category
regional_category_averages AS (
    SELECT
        region,
        category,
        AVG(category_revenue) AS avg_category_revenue
    FROM rep_category_revenue
    GROUP BY region, category
)

-- Final SELECT: combine underperformers with their category breakdown
SELECT
    u.rep_name,
    u.region,
    u.rep_q1_revenue,
    u.avg_regional_revenue,
    u.pct_of_regional_avg,
    rcr.category,
    rcr.category_revenue,
    rca.avg_category_revenue,
    ROUND(rcr.category_revenue / rca.avg_category_revenue * 100, 1) AS pct_of_category_avg,
    CASE
        WHEN rcr.category_revenue < rca.avg_category_revenue
        THEN 'Below Average'
        ELSE 'At or Above Average'
    END AS category_performance_flag
FROM underperformers u
JOIN rep_category_revenue rcr
    ON rcr.rep_id = u.rep_id
JOIN regional_category_averages rca
    ON rca.region = u.region
    AND rca.category = rcr.category
ORDER BY
    u.region,
    u.rep_q1_revenue,
    u.rep_name,
    rcr.category_revenue;

This is the complete query. Five CTEs, a final SELECT that joins three of them together, and a CASE WHEN expression that produces the human-readable performance flag. Let's walk through the design decisions.

Why keep CTE 4 separate from CTE 1? Because they aggregate at different granularities. CTE 1 groups by rep_id, rep_name, region — it doesn't care about category. CTE 4 groups by rep_id, region, category. They can't be the same query. You could argue that CTE 1 is actually redundant now — you could derive rep-level revenue by summing CTE 4 — but keeping them separate makes the intent of each CTE immediately legible.

Why not filter CTE 4 to only underperformers? We need regional category averages computed across all reps, not just underperformers. If we filtered to underperformers first, our "regional average" would only reflect underperformers, which would be meaningless — and would make every underperformer look closer to average than they actually are. The join to underperformers in the final SELECT handles the filtering at the right stage.

---

Step 6: Validate and Stress-Test Your Query

A query that returns results is not the same as a query that returns correct results. Before presenting this output to stakeholders, put it through deliberate stress tests.

Test 1: Boundary Verification

Pick one rep from your output. Manually calculate their total Q1 revenue by summing quantity * unit_price from order_items for their orders. Does it match what your query returns? If not, your join chain has a fan-out problem — more on that below.

Test 2: The Fan-Out Check

Fan-out is one of the most common and insidious bugs in multi-table analytical queries. It occurs when a JOIN multiplies rows unexpectedly, causing aggregates to be inflated.

In our schema, orders connects to order_items in a one-to-many relationship (one order has many line items), and customers connects to orders in a one-to-many relationship. This means when you JOIN from sales_reps down through customers → orders → order_items, each rep row gets expanded once per matching order item. That's the correct shape for this query because we're summing at the item level.

But imagine if the schema had a rep_territories table with multiple territory rows per rep, and you JOINed that in without aggregating on it. You'd get one row per territory per order item — your sum would be multiplied by the number of territories. This is a fan-out, and SUM will give you a number that's a multiple of the real total.

To check for fan-out, run a quick audit:

-- Count distinct orders vs total row count after joins
SELECT
    sr.rep_id,
    COUNT(DISTINCT o.order_id) AS distinct_orders,
    COUNT(o.order_id)          AS total_join_rows
FROM sales_reps sr
JOIN customers c    ON c.rep_id = sr.rep_id
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_date <  '2024-04-01'
  AND o.status = 'completed'
GROUP BY sr.rep_id;

If total_join_rows > distinct_orders, that's expected — each order has multiple items. But if the ratio looks wrong (say, exactly double what you'd expect), you likely have an unintended many-to-many join somewhere in the chain.

Test 3: The Null Cascade Check

What happens to reps who had zero completed orders in Q1? They won't appear in CTE 1 at all — they're not underperformers in the output, but they're not above-average performers either. They're invisible. Depending on the business question, this might be exactly wrong. Leadership might want to see reps with zero revenue flagged as the worst underperformers.

-- Find reps with no Q1 completed revenue
SELECT sr.rep_id, sr.rep_name, sr.region
FROM sales_reps sr
LEFT JOIN (
    SELECT DISTINCT c.rep_id
    FROM customers c
    JOIN orders o       ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_date <  '2024-04-01'
      AND o.status = 'completed'
) AS active_reps ON active_reps.rep_id = sr.rep_id
WHERE active_reps.rep_id IS NULL;

If this returns rows, you have reps who will be silently excluded from your report. Decide consciously whether that's acceptable or whether you need to modify CTE 1 to use a LEFT JOIN chain and handle the NULL revenue with COALESCE.

---

Step 7: Optimize for Performance

Our query runs five CTEs with multiple full-table joins. On a development database with thousands of rows, it'll be fast. On a production database with millions of orders and hundreds of thousands of order items, you need to think about performance.

Index Strategy

The columns we're filtering and joining on are the most critical to index:

-- Essential for the date range filter on orders
CREATE INDEX IF NOT EXISTS idx_orders_date_status
    ON orders(order_date, status);

-- Essential for the join from orders to customers
CREATE INDEX IF NOT EXISTS idx_orders_customer_id
    ON orders(customer_id);

-- Essential for the join from order_items to orders
CREATE INDEX IF NOT EXISTS idx_order_items_order_id
    ON order_items(order_id);

-- Essential for the join from order_items to products
CREATE INDEX IF NOT EXISTS idx_order_items_product_id
    ON order_items(product_id);

-- Essential for the join from customers to sales_reps
CREATE INDEX IF NOT EXISTS idx_customers_rep_id
    ON customers(rep_id);

A composite index on orders(order_date, status) means the database can satisfy the WHERE o.order_date >= ... AND o.status = 'completed' filter using a single index scan rather than filtering a full table scan. Whether order_date or status should be the leading column depends on cardinality — if status has only a few distinct values (low cardinality), put order_date first.

CTE Materialization Behavior

In PostgreSQL, CTEs have historically been "optimization fences" — each CTE is computed once and the result is materialized in memory, regardless of whether the planner could push filters from the outer query into the CTE. As of PostgreSQL 12, CTEs are inlined by default unless they contain side effects or you explicitly use MATERIALIZED. This matters for our query:

-- Force materialization if the CTE is expensive and referenced multiple times
-- (prevents re-evaluation)
rep_revenue AS MATERIALIZED (
    ...
)

Use MATERIALIZED when a CTE is expensive to compute and referenced multiple times in the outer query. Use the default (inlined) behavior when the CTE is cheap or only referenced once — the planner can then push predicates into it.

Partitioning Consideration

If your orders table is partitioned by order_date (a common setup for high-volume transactional tables), the date range filter will benefit from partition pruning — only the Q1 2024 partition needs to be scanned. This can reduce a multi-minute query to a few seconds. The lesson on SQL Query Optimization: Reading Execution Plans - Advanced Performance Analysis covers how to read the query plan to verify that partition pruning is actually happening.

---

Step 8: The Alternative — Window Functions

The CTE approach we've built is clear and maintainable. But there's an alternative worth knowing: window functions can compute regional averages inline, without the need for a separate aggregation CTE.

SELECT
    sr.rep_id,
    sr.rep_name,
    sr.region,
    SUM(oi.quantity * oi.unit_price)                      AS rep_q1_revenue,
    AVG(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY sr.region
    )                                                      AS avg_regional_revenue
FROM sales_reps sr
JOIN customers c    ON c.rep_id = sr.rep_id
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_date <  '2024-04-01'
  AND o.status = 'completed'
GROUP BY sr.rep_id, sr.rep_name, sr.region;

This is a beautiful pattern: AVG(SUM(...)) OVER (PARTITION BY ...) computes the average of the SUM aggregate across the window partition. The inner SUM is evaluated per-group (per rep), and then the AVG window function averages those per-group totals across all reps in the same region.

However, you can't filter on a window function in the same WHERE clause — window functions are evaluated after WHERE and after GROUP BY. To filter rows where rep_q1_revenue < avg_regional_revenue, you need to wrap this in a subquery:

SELECT *
FROM (
    SELECT
        sr.rep_id,
        sr.rep_name,
        sr.region,
        SUM(oi.quantity * oi.unit_price) AS rep_q1_revenue,
        AVG(SUM(oi.quantity * oi.unit_price)) OVER (
            PARTITION BY sr.region
        ) AS avg_regional_revenue
    FROM sales_reps sr
    JOIN customers c    ON c.rep_id = sr.rep_id
    JOIN orders o       ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_date <  '2024-04-01'
      AND o.status = 'completed'
    GROUP BY sr.rep_id, sr.rep_name, sr.region
) AS rep_with_regional_avg
WHERE rep_q1_revenue < avg_regional_revenue;

This is more concise than the CTE approach for the first part of the question, but the category breakdown still requires additional joins. The CTE architecture scales more gracefully to the full multi-part question, which is why we built it that way. The two approaches are not mutually exclusive — you can mix CTEs and window functions in the same query.

---

The Complete Final Query

Here's the full query, assembled cleanly:

WITH

rep_revenue AS (
    SELECT
        sr.rep_id,
        sr.rep_name,
        sr.region,
        SUM(oi.quantity * oi.unit_price) AS rep_q1_revenue
    FROM sales_reps sr
    JOIN customers c    ON c.rep_id = sr.rep_id
    JOIN orders o       ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_date <  '2024-04-01'
      AND o.status = 'completed'
    GROUP BY sr.rep_id, sr.rep_name, sr.region
),

regional_averages AS (
    SELECT
        region,
        AVG(rep_q1_revenue)  AS avg_regional_revenue,
        COUNT(rep_id)        AS rep_count_in_region
    FROM rep_revenue
    GROUP BY region
),

underperformers AS (
    SELECT
        rr.rep_id,
        rr.rep_name,
        rr.region,
        rr.rep_q1_revenue,
        ra.avg_regional_revenue,
        ra.rep_count_in_region,
        ROUND(rr.rep_q1_revenue / ra.avg_regional_revenue * 100, 1) AS pct_of_regional_avg
    FROM rep_revenue rr
    JOIN regional_averages ra ON ra.region = rr.region
    WHERE rr.rep_q1_revenue < ra.avg_regional_revenue
),

rep_category_revenue AS (
    SELECT
        sr.rep_id,
        sr.region,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS category_revenue
    FROM sales_reps sr
    JOIN customers c    ON c.rep_id = sr.rep_id
    JOIN orders o       ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    JOIN products p     ON p.product_id = oi.product_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_date <  '2024-04-01'
      AND o.status = 'completed'
    GROUP BY sr.rep_id, sr.region, p.category
),

regional_category_averages AS (
    SELECT
        region,
        category,
        AVG(category_revenue)  AS avg_category_revenue,
        COUNT(rep_id)          AS rep_count_selling_category
    FROM rep_category_revenue
    GROUP BY region, category
)

SELECT
    u.rep_name,
    u.region,
    u.rep_count_in_region,
    ROUND(u.rep_q1_revenue, 2)         AS rep_q1_revenue,
    ROUND(u.avg_regional_revenue, 2)   AS avg_regional_revenue,
    u.pct_of_regional_avg,
    rcr.category,
    ROUND(rcr.category_revenue, 2)     AS category_revenue,
    ROUND(rca.avg_category_revenue, 2) AS avg_category_revenue,
    ROUND(rcr.category_revenue / rca.avg_category_revenue * 100, 1) AS pct_of_category_avg,
    rca.rep_count_selling_category,
    CASE
        WHEN rcr.category_revenue < rca.avg_category_revenue
        THEN 'Below Average'
        ELSE 'At or Above Average'
    END AS category_performance_flag
FROM underperformers u
JOIN rep_category_revenue rcr
    ON rcr.rep_id = u.rep_id
JOIN regional_category_averages rca
    ON rca.region    = u.region
    AND rca.category = rcr.category
ORDER BY
    u.region,
    u.rep_q1_revenue ASC,
    u.rep_name,
    rcr.category_revenue ASC;

This is a production-grade analytical query. Five CTEs. Three tables in the final join. A CASE WHEN expression for the flag. Explicit ROUND() for clean numeric output. Sorted to surface the worst performers and worst categories at the top within each region and rep.

---

Hands-On Exercise

Work through this extension of the scenario. The business has come back with a follow-up:

"Of the underperforming reps you identified, which ones have at least one customer who placed more than 3 orders in Q1 but still generated below-average revenue from that customer? We think some reps are getting volume but losing margin."

This adds a layer: you need to find customers with high order frequency who are still low-revenue contributors for an underperforming rep. Here's how to approach it:

1. Add a new CTE that computes, per rep and per customer, the number of Q1 orders and the total revenue from that customer.
2. Filter that CTE to customers with more than 3 orders.
3. Join the result to your underperformers CTE.
4. Output rep name, customer company name, order count, customer revenue, and — as a bonus — the rep's total Q1 revenue for context.

Write this query before looking at the approach below.

-- Starter structure — fill in the CTEs you need

WITH

-- (Copy rep_revenue, regional_averages, underperformers from the main query)

customer_order_activity AS (
    SELECT
        c.rep_id,
        c.customer_id,
        cu.company_name,
        COUNT(DISTINCT o.order_id)       AS order_count,
        SUM(oi.quantity * oi.unit_price) AS customer_revenue
    FROM customers c
    JOIN orders o       ON o.customer_id = c.customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    JOIN customers cu   ON cu.customer_id = c.customer_id  -- self-reference for name
    WHERE o.order_date >= '2024-01-01'
      AND o.order_date <  '2024-04-01'
      AND o.status = 'completed'
    GROUP BY c.rep_id, c.customer_id, cu.company_name
    HAVING COUNT(DISTINCT o.order_id) > 3
)

SELECT
    u.rep_name,
    u.region,
    u.rep_q1_revenue,
    coa.company_name,
    coa.order_count,
    ROUND(coa.customer_revenue, 2) AS customer_revenue
FROM underperformers u
JOIN customer_order_activity coa ON coa.rep_id = u.rep_id
ORDER BY u.rep_name, coa.order_count DESC;

Notice the use of HAVING COUNT(DISTINCT o.order_id) > 3 in the CTE rather than in the final WHERE. This is a filter on an aggregate — exactly the kind of distinction that the lesson on Ranking and Filtering Groups with HAVING: Writing Conditional Aggregates That Go Beyond WHERE explores in detail.

---

Common Mistakes & Troubleshooting

Mistake 1: Aggregating Too Early and Losing Granularity

You collapse your data to the rep level in your first CTE, then later realize you need category-level data. Because you didn't include category in the first aggregation, it's gone. The fix: think about what you need in your final output before writing your first CTE. Work backwards from the output columns you want, and make sure you preserve the right granularity at each step.

Mistake 2: Filtering the Wrong Population for Averages

As mentioned earlier, filtering to underperformers before computing regional averages produces a circular, meaningless benchmark. Averages should always be computed across the full relevant population. If your business question is "below average among all reps," your average must include all reps.

Mistake 3: Division by Zero in Ratio Calculations

The pct_of_regional_avg calculation divides by avg_regional_revenue. If a region has no reps (which can happen if the only rep had zero revenue), avg_regional_revenue could be NULL or zero. Protect against this:

ROUND(
    rr.rep_q1_revenue / NULLIF(ra.avg_regional_revenue, 0) * 100,
    1
) AS pct_of_regional_avg

NULLIF(x, 0) returns NULL if x is zero, which propagates safely through arithmetic without throwing a division-by-zero error.

Mistake 4: Mismatched Join Keys Producing Cartesian Products

If you join rep_category_revenue to regional_category_averages on only region (forgetting category), every rep-category row joins to every regional average row in that region — not just the matching category. Your output explodes with nonsensical combinations. Always verify multi-column join keys are complete.

Mistake 5: Using CTE Names That Obscure Intent

CTEs named cte1, cte2, temp are unreadable to anyone inheriting your query, including future you. Name CTEs for what they represent: rep_revenue, underperformers, regional_category_averages. The query should read almost like a narrative.

---

Summary & Next Steps

You've built a complete, production-grade analytical query from first principles. The process we used — decompose the question, build from the inside out, validate at each layer, then optimize — is a repeatable methodology that applies to virtually any multi-part analytical problem you'll encounter.

The specific techniques covered:

• Layered CTE architecture for managing two-level aggregation problems without code duplication
• Aggregate-of-aggregates patterns (AVG of SUM, regional averages of rep totals) and why they require a second aggregation step
• Window functions as an alternative to aggregation CTEs for benchmark calculations, and their limitations around filtering
• JOIN order and fanout validation as part of query correctness verification
• NULL handling and population selection as analytical decisions, not just syntax choices

Where to go next:

If you want to extend the window function approach — particularly for ranking underperformers within their region or computing running totals — the lesson on Window Functions: RANK, ROW_NUMBER, and LAG is the natural next step.

For queries where the business question involves comparing a row to itself under different conditions — for example, comparing a rep's Q1 performance to their own Q4 of last year — the pattern covered in Writing SQL Self-Joins: Query the Same Table Twice to Compare Rows and Find Relationships is directly applicable.

And for scenarios where the question involves checking whether a rep exists in some subgroup — "which reps have never sold in a given category?" — the technique in Mastering SQL EXISTS and NOT EXISTS: Correlated Subquery Patterns for Filtering with Related Data fills that gap.

The ability to take a messy business question and turn it into a correct, readable, performant SQL query is what separates a data analyst from a data professional. You've just practiced that end-to-end.