Translating Business Questions into SQL: Decomposing Requirements into SELECT, JOIN, GROUP BY, and Subquery Steps

The product manager drops a question in Slack: "Which of our sales reps closed the most revenue last quarter among customers who signed up in the last two years, and how does that compare to their average deal size?" You stare at it. You know SQL. You know what GROUP BY does. But there's a gap between understanding individual clauses and knowing how to systematically turn a paragraph of business intent into a working, correct query.

That gap is what this lesson closes. The skill of decomposing requirements isn't about memorizing patterns — it's about developing a mental framework for reading business language, identifying the data structures underneath it, and building a query in deliberate, verifiable steps. Senior data professionals aren't faster because they type faster; they're faster because they've learned to front-load the thinking so the implementation almost writes itself.

By the end of this lesson, you'll have a repeatable decomposition method that works on questions ranging from simple summaries to multi-stage analytical queries. We'll work through three realistic business scenarios, building each query step by step, explaining why each clause goes where it does, and showing you the intermediate states so you can debug your own thinking — not just copy a finished answer.

What you'll learn:

• A five-step framework for translating any business question into SQL structure before writing a single line of code
• How to identify what each SQL clause maps to in natural language (result shape, data source, filtering logic, grouping dimensions, and subquery gates)
• How to sequence SELECT, JOIN, GROUP BY, HAVING, and subqueries by dependency order rather than writing order
• When to introduce a subquery vs. when a JOIN or HAVING clause is sufficient
• How to validate intermediate query steps so bugs surface early rather than in final output

---

Prerequisites

You should be comfortable with the fundamentals covered in SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries, understand how JOINs work conceptually (see SQL JOINs Explained with Real-World Examples), and have a working understanding of aggregate functions from Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG. This lesson doesn't introduce those concepts — it teaches you how to orchestrate them together under real analytical pressure.

The examples use standard SQL that runs on PostgreSQL, MySQL 8+, and SQL Server with minor dialect differences noted inline.

---

The Five-Step Decomposition Framework

Before touching a keyboard, you need to answer five questions about any business request. Each question maps directly to a SQL building block. Rushing past this step is the single biggest source of wrong or overcomplicated queries.

Step 1: What am I being asked to return? This defines your SELECT list — the columns and expressions in the final result set. Business questions often describe results in terms of "show me," "give me," or "I want to see." Extract the nouns and measures: names, counts, totals, ratios.

Step 2: What tables contain this data? This defines your FROM and JOIN structure. Every noun in your result set or filter lives in a specific table. Map each piece of information to its table, then determine how those tables connect — what foreign keys link them.

Step 3: What rows do I want to include or exclude? This defines your WHERE clause (and sometimes a subquery filter). Business questions use words like "only," "who," "in the last X days," "among customers that," or "excluding." These are filter signals.

Step 4: Do I need to group or summarize? This defines your GROUP BY and aggregate functions. If the answer involves "per rep," "by category," "for each region," or any running total, you have a grouping dimension. Identify what you're grouping by and what you're aggregating within each group.

Step 5: Are there conditions on the groups themselves? This defines your HAVING clause or a subquery gate. When the filter applies to an aggregated result ("reps who closed more than $100K," "products with fewer than 5 reviews"), you can't use WHERE — the aggregate doesn't exist yet at filter time.

Let's apply this framework to three escalating real-world scenarios.

---

Scenario 1: Sales Rep Performance Summary

The business question: "Show me each sales rep's name, the number of deals they closed, and their total revenue for Q3 2023."

This is a clean, single-layer question. Walk through the five steps:

Step 1 — What to return: Rep name, deal count, total revenue.

Step 2 — What tables: We need rep information (presumably a sales_reps table) and deal information (a deals table). Deals need a date field and a revenue field and a foreign key back to the rep.

Step 3 — Row filters: Only deals from Q3 2023, meaning closed between July 1 and September 30, 2023. Likely a filter on close_date.

Step 4 — Grouping: Results are "per rep," so we GROUP BY rep. COUNT(*) for deal count, SUM(revenue) for total.

Step 5 — Group conditions: None stated. Return all reps.

Now we build the query in dependency order: establish the data source and join first, add row filters, then aggregate.

-- Step 1: Verify the join works and returns expected rows
SELECT
    r.rep_id,
    r.first_name,
    r.last_name,
    d.deal_id,
    d.close_date,
    d.revenue
FROM sales_reps r
INNER JOIN deals d ON d.rep_id = r.rep_id
WHERE d.close_date >= '2023-07-01'
  AND d.close_date < '2023-10-01'
LIMIT 20;

Always run a raw join first before adding aggregates. This lets you see whether you have fan-out (more rows than expected due to a one-to-many relationship you didn't account for) and verify that your date filter is catching the right rows.

Once that looks right, add the aggregation:

-- Step 2: Add grouping and aggregation
SELECT
    r.rep_id,
    r.first_name,
    r.last_name,
    COUNT(d.deal_id)    AS deals_closed,
    SUM(d.revenue)      AS total_revenue
FROM sales_reps r
INNER JOIN deals d ON d.rep_id = r.rep_id
WHERE d.close_date >= '2023-07-01'
  AND d.close_date < '2023-10-01'
GROUP BY r.rep_id, r.first_name, r.last_name
ORDER BY total_revenue DESC;

Notice the GROUP BY includes rep_id, first_name, and last_name. You must include every non-aggregated column in your SELECT in the GROUP BY. This is one of the most common syntax errors beginners make, but there's a subtlety worth understanding: you technically only need to GROUP BY rep_id if it's the primary key, because first_name and last_name are functionally dependent on it. PostgreSQL enforces this strictly; some databases like MySQL (in older modes) don't. For portability and clarity, include all three. For a deeper dive into how GROUP BY and HAVING work together, see Master SQL Aggregate Functions: Advanced GROUP BY, HAVING, and Performance Optimization.

What if you want reps with zero deals? Switch INNER JOIN to LEFT JOIN. Now reps who had no deals in Q3 appear with NULL deal counts and revenue. But notice the problem — your WHERE clause on close_date will filter out those reps again, because NULL is not between July 1 and October 1.

-- Correct approach: move the date filter into the JOIN condition
SELECT
    r.rep_id,
    r.first_name,
    r.last_name,
    COUNT(d.deal_id)         AS deals_closed,
    COALESCE(SUM(d.revenue), 0) AS total_revenue
FROM sales_reps r
LEFT JOIN deals d
    ON d.rep_id = r.rep_id
    AND d.close_date >= '2023-07-01'
    AND d.close_date < '2023-10-01'
GROUP BY r.rep_id, r.first_name, r.last_name
ORDER BY total_revenue DESC;

Moving the date filter into the ON clause applies it before the join rather than after, preserving reps with no matching deals as NULLs rather than eliminating them. This is a fundamental JOIN semantics distinction that trips up even experienced analysts.

---

Scenario 2: Filtering by Aggregate Conditions

The business question: "Which product categories had more than 500 orders last month, and what was the average order value per category?"

This introduces the HAVING clause — Step 5 of our framework.

Step 1 — What to return: Category name, order count, average order value.

Step 2 — What tables: orders, order_items (to get to product), products (to get category). Alternatively, if category is denormalized onto the order or product table, fewer joins. We'll assume a normalized schema: orders → order_items → products (which has a category column).

Step 3 — Row filters: Last month. This is date arithmetic — we want orders from the calendar month prior to the current date. This varies by database dialect.

Step 4 — Grouping: Per category. Aggregates: COUNT of orders, AVG of order value.

Step 5 — Group conditions: Only categories where the count exceeds 500. This goes in HAVING, not WHERE.

Start by verifying the three-table join:

-- Verify join chain and row counts
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    p.category
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.order_id
INNER JOIN products p ON p.product_id = oi.product_id
WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND o.order_date <  DATE_TRUNC('month', CURRENT_DATE)
LIMIT 20;

There are two fixes. One: count distinct order IDs instead of rows. Two: aggregate order-level data before joining to the product dimension. Let's use COUNT(DISTINCT) for simplicity here:

SELECT
    p.category,
    COUNT(DISTINCT o.order_id)                AS order_count,
    ROUND(AVG(o.total_amount), 2)             AS avg_order_value
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.order_id
INNER JOIN products p ON p.product_id = oi.product_id
WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND o.order_date <  DATE_TRUNC('month', CURRENT_DATE)
GROUP BY p.category
HAVING COUNT(DISTINCT o.order_id) > 500
ORDER BY order_count DESC;

Notice we repeat the aggregate expression in HAVING rather than referencing the alias order_count. SQL evaluates HAVING before SELECT aliases are resolved in most databases, so HAVING order_count > 500 will fail in PostgreSQL and SQL Server (though MySQL and some others allow it). Write the full expression to be safe and portable. This is explained in detail in Ranking and Filtering Groups with HAVING: Writing Conditional Aggregates That Go Beyond WHERE.

Now, there's a subtlety with AVG(o.total_amount) in this multi-join context. Because o.total_amount comes from the orders table, and each order appears once per line item, the AVG is averaging the same order amount multiple times for multi-item orders. For an accurate average order value, you'd want to de-duplicate at the order level first:

-- More accurate: pre-aggregate orders, then join to products
SELECT
    p.category,
    COUNT(DISTINCT o.order_id)    AS order_count,
    ROUND(AVG(o.total_amount), 2) AS avg_order_value
FROM (
    -- De-duplicate to one row per order
    SELECT order_id, MIN(order_date) AS order_date, MAX(total_amount) AS total_amount
    FROM orders
    WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
      AND order_date <  DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY order_id
) o
INNER JOIN order_items oi ON oi.order_id = o.order_id
INNER JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category
HAVING COUNT(DISTINCT o.order_id) > 500
ORDER BY order_count DESC;

This subquery in the FROM clause — an inline view or derived table — is an important tool. It lets you transform data before it participates in a join, preventing the fan-out from corrupting your aggregates. You're not avoiding joins; you're controlling the granularity of the data entering the join.

---

Scenario 3: The Multi-Layer Question

The business question: "Which sales reps closed the most revenue last quarter among customers who signed up in the last two years, and how does that compare to their average deal size?"

This is the Slack message from the introduction, and it's genuinely more complex. Let's decompose it carefully.

Step 1 — What to return: Rep name, total revenue, average deal size. The phrase "how does that compare" is asking for both the total and the average in the same result row.

Step 2 — What tables: sales_reps, deals, customers. Deals link to both a rep and a customer.

Step 3 — Row filters: Two distinct filters operating at different levels:

• Deals from last quarter (a filter on the deal itself)
• Customers who signed up in the last two years (a filter on the customer dimension)

The second filter is what introduces complexity — it's a filter on a related entity, not on the primary fact row. This is a subquery signal.

Step 4 — Grouping: Per rep. Aggregate: SUM(revenue), AVG(revenue) — both on deal revenue.

Step 5 — Group conditions: "Most revenue" suggests ordering, not HAVING. But we might want to add a HAVING to show only reps above a threshold if the question implied that.

The customer signup filter is the key design decision. You have three options:

Option A: Filter with a WHERE clause on a joined customers table. Option B: Filter with a subquery in the WHERE clause (WHERE customer_id IN (SELECT ...)). Option C: Filter using EXISTS.

All three can produce identical results, but they have different performance profiles and readability characteristics. Let's build Option A first since it's most intuitive, then look at the tradeoffs.

-- Option A: Filter via joined customers table
SELECT
    r.rep_id,
    r.first_name,
    r.last_name,
    COUNT(d.deal_id)            AS deals_closed,
    SUM(d.revenue)              AS total_revenue,
    ROUND(AVG(d.revenue), 2)    AS avg_deal_size
FROM sales_reps r
INNER JOIN deals d
    ON d.rep_id = r.rep_id
   AND d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
   AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
INNER JOIN customers c
    ON c.customer_id = d.customer_id
   AND c.signup_date >= CURRENT_DATE - INTERVAL '2 years'
GROUP BY r.rep_id, r.first_name, r.last_name
ORDER BY total_revenue DESC;

Now Option B — subquery in WHERE:

-- Option B: Subquery filter for qualifying customers
SELECT
    r.rep_id,
    r.first_name,
    r.last_name,
    COUNT(d.deal_id)            AS deals_closed,
    SUM(d.revenue)              AS total_revenue,
    ROUND(AVG(d.revenue), 2)    AS avg_deal_size
FROM sales_reps r
INNER JOIN deals d ON d.rep_id = r.rep_id
WHERE d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
  AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
  AND d.customer_id IN (
      SELECT customer_id
      FROM customers
      WHERE signup_date >= CURRENT_DATE - INTERVAL '2 years'
  )
GROUP BY r.rep_id, r.first_name, r.last_name
ORDER BY total_revenue DESC;

The IN subquery is cleaner when the customer filter is the only reason you need the customers table. You're not selecting any customer attributes in the output — you just want to restrict deals to those involving qualifying customers. The subquery communicates that intent directly: "from deals, but only where the customer passes this check."

Option C uses EXISTS, which is semantically identical to IN for this case but behaves differently under the hood in some databases:

-- Option C: EXISTS correlated subquery
SELECT
    r.rep_id,
    r.first_name,
    r.last_name,
    COUNT(d.deal_id)            AS deals_closed,
    SUM(d.revenue)              AS total_revenue,
    ROUND(AVG(d.revenue), 2)    AS avg_deal_size
FROM sales_reps r
INNER JOIN deals d ON d.rep_id = r.rep_id
WHERE d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
  AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
  AND EXISTS (
      SELECT 1
      FROM customers c
      WHERE c.customer_id = d.customer_id
        AND c.signup_date >= CURRENT_DATE - INTERVAL '2 years'
  )
GROUP BY r.rep_id, r.first_name, r.last_name
ORDER BY total_revenue DESC;

EXISTS short-circuits as soon as one matching row is found, which can outperform IN when the subquery would return a large result set. Modern query optimizers often rewrite IN as a semi-join anyway, making the practical difference smaller than it used to be. For more on these patterns, see Mastering SQL EXISTS and NOT EXISTS: Correlated Subquery Patterns for Filtering with Related Data.

Now let's add the complexity the business question actually implies: ranking the reps. "Which reps closed the most revenue" suggests the business wants a ranked list, possibly the top N. If you want the top 5:

SELECT
    r.rep_id,
    r.first_name,
    r.last_name,
    COUNT(d.deal_id)            AS deals_closed,
    SUM(d.revenue)              AS total_revenue,
    ROUND(AVG(d.revenue), 2)    AS avg_deal_size
FROM sales_reps r
INNER JOIN deals d ON d.rep_id = r.rep_id
WHERE d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
  AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
  AND d.customer_id IN (
      SELECT customer_id
      FROM customers
      WHERE signup_date >= CURRENT_DATE - INTERVAL '2 years'
  )
GROUP BY r.rep_id, r.first_name, r.last_name
ORDER BY total_revenue DESC
LIMIT 5;

---

When Subqueries Are Required vs. Optional

One of the most important judgment calls in SQL authorship is deciding when a subquery is necessary versus when it's merely one approach among several. Understanding this prevents both under-using subqueries (writing 5-table joins when a simple IN would do) and over-using them (nesting queries four levels deep when a CTE would communicate the same logic in half the complexity).

Subqueries are required when:

1. You need to filter on an aggregate result — and the aggregate involves a different level of granularity than the outer query. Example: "show me customers whose average order value exceeds the overall average."

-- Can't do this in WHERE — AVG(order_value) doesn't exist at row-filter time
SELECT customer_id, AVG(order_value) AS avg_order
FROM orders
GROUP BY customer_id
HAVING AVG(order_value) > (
    SELECT AVG(order_value) FROM orders
);

The inner query computes a scalar value (the overall average). The outer HAVING compares each group's average against it. This cannot be expressed as a flat query — the two aggregations operate at different levels of grouping.

2. You need to pre-aggregate before joining — to prevent fan-out inflation as we saw in Scenario 2.

3. You need a derived dimension that doesn't exist in raw tables — for example, "categorize customers by their total lifetime value tier, then count how many deals we closed per tier."

SELECT
    ltv_bucket,
    COUNT(*) AS deals_closed,
    SUM(d.revenue) AS total_revenue
FROM deals d
INNER JOIN (
    SELECT
        customer_id,
        SUM(order_value) AS lifetime_value,
        CASE
            WHEN SUM(order_value) >= 10000 THEN 'High'
            WHEN SUM(order_value) >= 1000  THEN 'Mid'
            ELSE 'Low'
        END AS ltv_bucket
    FROM orders
    GROUP BY customer_id
) c ON c.customer_id = d.customer_id
WHERE d.close_date >= '2023-01-01'
GROUP BY ltv_bucket
ORDER BY total_revenue DESC;

The subquery creates a derived dimension (ltv_bucket) that doesn't exist anywhere in the raw tables. The outer query then uses it as a grouping key. For a deeper look at using derived tables and inline views this way, see Writing SQL FROM Scratch: Structuring Multi-Step Analytical Queries with Derived Tables and Inline Views.

Subqueries are optional (and a JOIN may be cleaner) when:

• You need data from another table in your SELECT list (use JOIN, not a correlated scalar subquery — the latter can execute once per row)
• You're filtering on a foreign key attribute that appears in a table you'd join anyway
• The subquery result is a simple lookup that returns exactly one value

---

Converting Complex Business Language into Query Structure

Business questions rarely arrive pre-parsed into logical steps. Let's build a translation vocabulary.

Business phrase	SQL translation
"For each / per / by [dimension]"	GROUP BY [dimension]
"Total / sum of / aggregate"	SUM()
"Average / mean"	AVG()
"How many / count of"	COUNT()
"Only / where / among / excluding"	WHERE or HAVING
"Who have / that have / with more than"	HAVING (if on aggregate), WHERE (if on attribute)
"Top N / highest / most"	ORDER BY ... DESC LIMIT N
"Compared to / versus"	Multiple aggregates in same SELECT, or self-join/UNION
"In the last X days/months"	date column >= CURRENT_DATE - INTERVAL 'X [unit]'
"Who have never / who haven't"	NOT EXISTS or LEFT JOIN ... WHERE ... IS NULL
"At least once"	EXISTS or IN
"Whose [aggregate] exceeds [aggregate]"	Subquery in HAVING

Let's apply this to a genuinely ambiguous question that requires interpretation: "Show me customers who are spending more than they used to."

This is underspecified. Before writing SQL, you need to ask clarifying questions:

• What time periods define "now" vs. "used to"? (Last 90 days vs. prior 90 days? This year vs. last year?)
• Is "spending" total order value, number of orders, or average order value?
• Do you want the raw amounts, the difference, or the percentage change?

Let's assume: compare each customer's total spend in the last 90 days to the 90 days before that, and return customers where recent spend is higher, along with the change.

Step 1: Return customer ID/name, recent spend, prior spend, change amount. Step 2: customers, orders. Orders have a date and amount. Step 3: Two time windows — rows need to be partitioned by date range. Step 4: Sum per customer per period. Step 5: Filter where recent > prior.

The natural structure is two aggregations, one per time period, then a comparison. Using conditional aggregation with CASE WHEN is cleaner than two separate subqueries:

SELECT
    c.customer_id,
    c.full_name,
    SUM(CASE
        WHEN o.order_date >= CURRENT_DATE - INTERVAL '90 days'
        THEN o.order_value ELSE 0
    END) AS recent_spend,
    SUM(CASE
        WHEN o.order_date >= CURRENT_DATE - INTERVAL '180 days'
         AND o.order_date <  CURRENT_DATE - INTERVAL '90 days'
        THEN o.order_value ELSE 0
    END) AS prior_spend,
    SUM(CASE
        WHEN o.order_date >= CURRENT_DATE - INTERVAL '90 days'
        THEN o.order_value ELSE 0
    END)
    -
    SUM(CASE
        WHEN o.order_date >= CURRENT_DATE - INTERVAL '180 days'
         AND o.order_date <  CURRENT_DATE - INTERVAL '90 days'
        THEN o.order_value ELSE 0
    END) AS spend_increase
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '180 days'
GROUP BY c.customer_id, c.full_name
HAVING
    SUM(CASE
        WHEN o.order_date >= CURRENT_DATE - INTERVAL '90 days'
        THEN o.order_value ELSE 0
    END)
    >
    SUM(CASE
        WHEN o.order_date >= CURRENT_DATE - INTERVAL '180 days'
         AND o.order_date <  CURRENT_DATE - INTERVAL '90 days'
        THEN o.order_value ELSE 0
    END)
ORDER BY spend_increase DESC;

The HAVING clause here compares two aggregated values — something you absolutely cannot do in WHERE. This is the CASE WHEN aggregation pattern, one of the most powerful tools for computing multiple summaries across different subsets of rows without multiple subqueries. For more on this technique, see Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice.

Rewritten with a CTE:

WITH customer_spend AS (
    SELECT
        c.customer_id,
        c.full_name,
        SUM(CASE
            WHEN o.order_date >= CURRENT_DATE - INTERVAL '90 days'
            THEN o.order_value ELSE 0
        END) AS recent_spend,
        SUM(CASE
            WHEN o.order_date >= CURRENT_DATE - INTERVAL '180 days'
             AND o.order_date <  CURRENT_DATE - INTERVAL '90 days'
            THEN o.order_value ELSE 0
        END) AS prior_spend
    FROM customers c
    INNER JOIN orders o ON o.customer_id = c.customer_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '180 days'
    GROUP BY c.customer_id, c.full_name
)
SELECT
    customer_id,
    full_name,
    recent_spend,
    prior_spend,
    (recent_spend - prior_spend) AS spend_increase
FROM customer_spend
WHERE recent_spend > prior_spend
ORDER BY spend_increase DESC;

The outer WHERE here filters on computed columns (recent_spend, prior_spend) that are now real columns in the CTE's result set — not aggregates. So WHERE is correct in the outer query; HAVING would be incorrect. This is why the CTE rewrite also clarifies your thinking about what's a row filter versus a group filter.

---

Structuring Multi-Step Queries: When to Use CTEs vs. Nested Subqueries

As queries grow to three or more logical steps, readability becomes a real concern. Both CTEs and nested subqueries can express the same logic, but they differ significantly in how they communicate intent. For a thorough treatment, see Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture.

Nested subqueries are compact but read inside-out, which is the opposite of how most people think about a problem:

SELECT rep_id, total_revenue
FROM (
    SELECT rep_id, SUM(revenue) AS total_revenue
    FROM deals
    WHERE customer_id IN (
        SELECT customer_id FROM customers WHERE tier = 'Enterprise'
    )
    GROUP BY rep_id
) rep_totals
WHERE total_revenue > 50000;

CTEs read top-to-bottom and name each step, which maps naturally to the decomposition framework:

-- Step 1: Identify qualifying customers
WITH enterprise_customers AS (
    SELECT customer_id
    FROM customers
    WHERE tier = 'Enterprise'
),

-- Step 2: Aggregate deal revenue for qualifying customers
rep_totals AS (
    SELECT
        d.rep_id,
        SUM(d.revenue) AS total_revenue
    FROM deals d
    INNER JOIN enterprise_customers ec ON ec.customer_id = d.customer_id
    GROUP BY d.rep_id
)

-- Step 3: Filter to reps above threshold
SELECT rep_id, total_revenue
FROM rep_totals
WHERE total_revenue > 50000
ORDER BY total_revenue DESC;

The CTE version requires more lines but produces several advantages: each step is independently readable and testable, you can add a SELECT * FROM enterprise_customers to verify step 1 before running the full query, and the step names serve as inline documentation.

---

Hands-On Exercise

Work through this business question using the five-step framework. Write your decomposition before looking at the solution outline below.

The question: "For each product category, show me the month with the highest number of distinct customers who placed an order. Only include months in the last 12 months, and only show categories where at least 3 distinct months had more than 100 customers."

Schema:

• orders(order_id, customer_id, order_date, category, order_total)

Your decomposition (fill this out):

1. What to return: ___
2. What tables: ___
3. Row filters: ___
4. Grouping: ___
5. Group conditions: ___

Solution outline:

Step 1: Category, month, customer count. The "month with the highest" implies we need a ranking within category — this is a subquery or window function problem. Let's use a subquery approach.

Step 2: Just orders — category and customer and date are all on the same table.

Step 3: order_date within the last 12 months.

Step 4: Group by category and month to count distinct customers per month per category.

Step 5: Only months where customer count > 100 (inner filter); only categories where at least 3 such months exist (outer filter).

-- Step 1: Count distinct customers per category per month
WITH monthly_counts AS (
    SELECT
        category,
        DATE_TRUNC('month', order_date)  AS order_month,
        COUNT(DISTINCT customer_id)      AS distinct_customers
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY category, DATE_TRUNC('month', order_date)
),

-- Step 2: Identify categories with at least 3 months above 100 customers
qualifying_categories AS (
    SELECT category
    FROM monthly_counts
    WHERE distinct_customers > 100
    GROUP BY category
    HAVING COUNT(*) >= 3
),

-- Step 3: For qualifying categories, find the peak month
peak_month AS (
    SELECT
        mc.category,
        mc.order_month,
        mc.distinct_customers,
        RANK() OVER (
            PARTITION BY mc.category
            ORDER BY mc.distinct_customers DESC
        ) AS rnk
    FROM monthly_counts mc
    INNER JOIN qualifying_categories qc ON qc.category = mc.category
)

SELECT
    category,
    TO_CHAR(order_month, 'YYYY-MM') AS peak_month,
    distinct_customers              AS peak_customer_count
FROM peak_month
WHERE rnk = 1
ORDER BY peak_customer_count DESC;

This query chains three CTEs, each answering one piece of the business question. The window function RANK() handles ties gracefully (two months with identical customer counts both get rank 1). Notice that RANK() here is used only for selection in the final WHERE clause — we don't need to surface the rank itself in the output.

---

Common Mistakes & Troubleshooting

Mistake 1: Filtering on aggregate values in WHERE

-- WRONG: WHERE can't see aggregates
SELECT rep_id, SUM(revenue) AS total_revenue
FROM deals
WHERE SUM(revenue) > 100000  -- ❌ SUM doesn't exist here yet
GROUP BY rep_id;

-- CORRECT: use HAVING
SELECT rep_id, SUM(revenue) AS total_revenue
FROM deals
GROUP BY rep_id
HAVING SUM(revenue) > 100000;  -- ✓

Mistake 2: Fan-out from JOIN inflating aggregates

-- WRONG: order total summed multiple times per line item
SELECT c.customer_id, SUM(o.total_amount) AS 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
GROUP BY c.customer_id;

-- CORRECT: aggregate order-level data before joining to line items
-- (or use COUNT DISTINCT if you only need counts, not sums)
SELECT c.customer_id, SUM(o_agg.total_amount) AS revenue
FROM customers c
JOIN (
    SELECT customer_id, SUM(total_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) o_agg ON o_agg.customer_id = c.customer_id
GROUP BY c.customer_id;

Mistake 3: WHERE clause eliminating LEFT JOIN NULLs

-- WRONG: the WHERE clause kills the LEFT JOIN's purpose
SELECT r.rep_id, COUNT(d.deal_id) AS deal_count
FROM sales_reps r
LEFT JOIN deals d ON d.rep_id = r.rep_id
WHERE d.close_date > '2023-01-01';  -- ❌ nulls from LEFT JOIN fail this

-- CORRECT: filter in ON clause or keep the IS NULL escape hatch
SELECT r.rep_id, COUNT(d.deal_id) AS deal_count
FROM sales_reps r
LEFT JOIN deals d
    ON d.rep_id = r.rep_id
   AND d.close_date > '2023-01-01'  -- ✓ applied before join
GROUP BY r.rep_id;

Mistake 4: Missing GROUP BY columns

-- WRONG: selecting first_name without grouping on it
SELECT rep_id, first_name, SUM(revenue)
FROM deals
JOIN sales_reps USING (rep_id)
GROUP BY rep_id;  -- ❌ first_name must be in GROUP BY (in strict databases)

-- CORRECT
SELECT rep_id, first_name, SUM(revenue)
FROM deals
JOIN sales_reps USING (rep_id)
GROUP BY rep_id, first_name;  -- ✓

Mistake 5: Using DISTINCT where GROUP BY is more appropriate

-- Inefficient for large datasets
SELECT DISTINCT customer_id, order_date FROM orders;

-- Often better: be explicit about what you want
SELECT customer_id, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id;

DISTINCT is a blunt instrument — it deduplicates the entire row. If you actually want one row per customer with specific aggregate behavior, GROUP BY makes your intent explicit and often executes more efficiently. See Selecting Distinct Values and Eliminating Duplicates in SQL: DISTINCT, GROUP BY, and COUNT Explained for a full treatment.

Mistake 6: Building the whole query at once without testing intermediate steps

The most common debugging trap is writing a 30-line query, getting a wrong answer, and having no idea which step is broken. The discipline of running the raw JOIN before adding aggregates, and running the aggregation before adding HAVING, is not optional — it's what separates analysts who debug in 2 minutes from analysts who debug in 2 hours.

---

Summary & Next Steps

You now have a systematic approach to translating business intent into SQL structure. The core discipline is front-loading the thinking: identify what you're returning, where it lives, what rows qualify, how they aggregate, and whether groups need filtering — before writing a line of code.

The key principles to internalize:

• Write queries in thinking order, not execution order. Start with the result shape, then work backward to data sources.
• Run incremental steps. Test the JOIN before adding aggregation. Test aggregation before adding HAVING.
• Choose your filter location deliberately. WHERE for row-level filters, HAVING for group-level filters, ON for pre-join filters in outer joins.
• Recognize fan-out. Any time you join to a "many" side, your row count grows. Know whether that inflates your aggregates and if so, pre-aggregate or use COUNT(DISTINCT).
• Subqueries serve a structural purpose. Use them when you need a different granularity, a derived dimension, or a scalar aggregate comparison — not just because they feel powerful.
• Name your steps. CTEs turn implicit query structure into explicit, documented logic. They're worth the extra lines.

From here, deepen your ability to handle more complex scenarios. Writing Multi-Step Analytical Queries: Chaining Subqueries, JOINs, and GROUP BY to Answer Real Business Questions extends these techniques into full analytical workflows. For queries that need to rank, compare sequential time periods, or compute running totals, Window Functions: RANK, ROW_NUMBER, and LAG introduces a layer of expressiveness that JOIN and GROUP BY alone can't replicate. And when your queries grow to five or more CTEs, Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture covers how to structure, test, and optimize them at scale.

The business questions don't get simpler. Your decomposition framework does.