Progressive Scenarios: Translating Business Questions into Correct Join Patterns

From Business Question to Join Pattern: A Progressive Workflow

In real work, you rarely start with “I need a LEFT JOIN.” You start with a business question: “Which customers haven’t ordered in 90 days?” or “Why doesn’t this revenue number match finance?” The join pattern is a consequence of the question, the grain (row-level meaning) of each table, and the required completeness of the output.

This chapter builds a progressive workflow for translating business questions into correct join patterns without rehashing join definitions. The focus is on: (1) identifying the output grain, (2) choosing the “anchor” dataset that must be preserved, (3) deciding which relationships are optional vs required, (4) controlling row multiplication, and (5) validating results with targeted checks.

Step 1: Write the question as an output contract

Before writing SQL, rewrite the question as a contract that specifies:

• Entity and grain: “one row per customer”, “one row per order line”, “one row per month per product”.
• Required completeness: include entities even if related data is missing? (e.g., include customers with no orders).
• Measures and attributes: which columns are displayed, which are aggregated, and which are used only for filtering.
• Time window: whether time filters apply to the anchor entity, the related entity, or both.

This contract is what determines the join pattern. If you skip it, you’ll often “get an answer” that is subtly answering a different question.

Step 2: Choose the anchor dataset (the table you must not lose)

The anchor is the dataset whose rows define the output grain and must be preserved. Many join mistakes are simply anchoring on the wrong table. A quick heuristic: ask “If I ran the query with no joins, which table would I start from to get the correct list of rows?” That table (or a derived set like a calendar) is your anchor.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

Step 3: Classify each relationship as required or optional

For each additional table, decide whether the relationship is:

• Required: rows without a match should be excluded from the final output (e.g., “only customers who placed an order”).
• Optional: rows without a match should still appear (e.g., “all customers, whether or not they have a loyalty profile”).

This classification drives whether you preserve the anchor rows and how you filter. It also affects where you place predicates (in the join condition vs in the filter stage) to avoid changing the intended completeness.

Step 4: Decide whether you need detail rows or aggregated facts

Many business questions want one row per entity with metrics (counts, sums). If you join raw detail tables directly, you can accidentally multiply rows and inflate metrics. A safe pattern is to aggregate the detail table to the intended grain first, then join the aggregated result to the anchor.

When you do need detail rows (e.g., “show each order line with product and customer”), you still need to be explicit about which table defines the grain and whether you expect one-to-one or one-to-many relationships.

Scenario 1: “Show all customers and their most recent order date (if any)”

Output contract: one row per customer; include customers with no orders; show last_order_date.

Reasoning: The anchor is customers. Orders are optional. Because the output is one row per customer, you should not join to raw orders and then group by customer unless you are careful; instead, compute last order per customer in a subquery/CTE and join that result.

WITH last_order AS (  SELECT    o.customer_id,    MAX(o.order_date) AS last_order_date  FROM orders o  GROUP BY o.customer_id)SELECT  c.customer_id,  c.customer_name,  lo.last_order_dateFROM customers cLEFT JOIN last_order lo  ON lo.customer_id = c.customer_idORDER BY c.customer_id;

Step-by-step checks:

• Count of output rows should equal count of customers (unless you intentionally filter customers).
• Customers with no orders should have last_order_date as null.
• Spot-check a few customers against the orders table.

Scenario 2: “Customers who have never placed an order” (anti-join pattern)

Output contract: one row per customer; include only those with zero orders.

Reasoning: Anchor is customers. Orders are optional, but the question asks for missing matches. A robust approach is to join to a distinct list of customers who have orders (or an aggregated orders-per-customer) and then filter for null on the joined side.

WITH ordered_customers AS (  SELECT DISTINCT o.customer_id  FROM orders o)SELECT  c.customer_id,  c.customer_nameFROM customers cLEFT JOIN ordered_customers oc  ON oc.customer_id = c.customer_idWHERE oc.customer_id IS NULLORDER BY c.customer_id;

Practical notes: Using a distinct/aggregated subquery prevents duplicates from multiple orders. This is especially important if you later add more joins (e.g., to customer segments) and want stable row counts.

Scenario 3: “Revenue by month, including months with zero revenue” (calendar anchoring)

Output contract: one row per month in a given range; show revenue; months with no sales should appear with 0.

Reasoning: The anchor is not a business entity table; it’s a calendar (date dimension) or a generated month series. Sales are optional relative to the calendar. The join must preserve the calendar months.

WITH months AS (  SELECT month_start  FROM dim_month  WHERE month_start BETWEEN DATE '2025-01-01' AND DATE '2025-12-01'),monthly_revenue AS (  SELECT    DATE_TRUNC('month', o.order_date) AS month_start,    SUM(o.order_total) AS revenue  FROM orders o  WHERE o.order_date >= DATE '2025-01-01'    AND o.order_date <  DATE '2026-01-01'  GROUP BY DATE_TRUNC('month', o.order_date))SELECT  m.month_start,  COALESCE(r.revenue, 0) AS revenueFROM months mLEFT JOIN monthly_revenue r  ON r.month_start = m.month_startORDER BY m.month_start;

Step-by-step checks:

• Verify you get exactly 12 rows for 2025.
• Confirm months with no orders show 0, not missing rows.
• Confirm the time filter is applied to orders in the revenue CTE, not to the calendar anchor.

Scenario 4: “Orders with their latest shipment status” (joining to ‘latest’ related row)

Output contract: one row per order; include orders even if not shipped; show latest shipment status if present.

Reasoning: Anchor is orders. Shipments are optional. The tricky part is “latest shipment status”: shipments is one-to-many per order, so you must reduce it to one row per order before joining, otherwise you’ll duplicate orders.

One portable approach is to compute the max shipped_at per order and join back to shipments to get the status. (Some databases support window functions; if you use them, the idea is the same: pick one row per order.)

WITH latest_shipment_time AS (  SELECT    s.order_id,    MAX(s.updated_at) AS latest_updated_at  FROM shipments s  GROUP BY s.order_id),latest_shipment AS (  SELECT    s.order_id,    s.status,    s.updated_at  FROM shipments s  JOIN latest_shipment_time lst    ON lst.order_id = s.order_id   AND lst.latest_updated_at = s.updated_at)SELECT  o.order_id,  o.order_date,  o.order_total,  ls.status AS latest_shipment_status,  ls.updated_at AS shipment_status_timeFROM orders oLEFT JOIN latest_shipment ls  ON ls.order_id = o.order_idORDER BY o.order_id;

Practical notes: If two shipment rows share the same updated_at for an order, the join back can return duplicates. In that case, you need a tie-breaker (e.g., shipment_id max) or a window function with deterministic ordering.

Scenario 5: “Find mismatches between two systems” (reconciliation pattern)

Business question: “Which invoices exist in billing but not in accounting, and which exist in accounting but not in billing?”

Output contract: one row per invoice_id across both systems; show presence/absence and key attributes; include unmatched from either side.

Reasoning: This is a reconciliation problem where you need symmetric visibility. The join pattern must allow rows from both sides to appear. Then you classify each row as “only in A”, “only in B”, or “in both”.

SELECT  COALESCE(b.invoice_id, a.invoice_id) AS invoice_id,  b.amount AS billing_amount,  a.amount AS accounting_amount,  CASE    WHEN b.invoice_id IS NOT NULL AND a.invoice_id IS NOT NULL THEN 'in_both'    WHEN b.invoice_id IS NOT NULL AND a.invoice_id IS NULL THEN 'only_in_billing'    WHEN b.invoice_id IS NULL AND a.invoice_id IS NOT NULL THEN 'only_in_accounting'  END AS reconciliation_statusFROM billing_invoices bFULL JOIN accounting_invoices a  ON a.invoice_id = b.invoice_id;

Step-by-step checks:

• Count invoices in each source separately; compare to counts by reconciliation_status.
• For “in_both”, compare amounts and dates; add additional columns to diagnose differences.
• If invoice_id is not unique in either system, dedupe or aggregate first to avoid false mismatches.

Scenario 6: “Top products by revenue, but include products with no sales” (dimension completeness)

Output contract: one row per product; include products with zero sales; show revenue; then rank or filter.

Reasoning: Anchor is products (dimension). Sales facts are optional. Aggregate sales to product grain, then join. If you filter to “top products,” be careful: applying a filter like WHERE revenue > 0 changes the question by excluding zero-sales products. If the business wants “top among all products,” you can still compute ranks while keeping zeros, then filter by rank.

WITH product_revenue AS (  SELECT    oi.product_id,    SUM(oi.quantity * oi.unit_price) AS revenue  FROM order_items oi  GROUP BY oi.product_id),product_with_revenue AS (  SELECT    p.product_id,    p.product_name,    COALESCE(pr.revenue, 0) AS revenue  FROM products p  LEFT JOIN product_revenue pr    ON pr.product_id = p.product_id)SELECT  product_id,  product_name,  revenueFROM product_with_revenueORDER BY revenue DESC, product_id;

Practical extension: If you need “top 10,” use a ranking function if available, or limit after ordering. Keep in mind ties and deterministic ordering.

Scenario 7: “Conversion funnel: visits → signups → purchases” (multi-stage optionality)

Business question: “For each marketing campaign, how many visitors signed up, and how many of those signed up users purchased?”

Output contract: one row per campaign; include campaigns even if they have visits but no signups, and even if signups but no purchases; show counts at each stage.

Reasoning: The anchor is campaigns (or a campaign list for the period). Each stage is optional relative to the previous stage depending on the question. A reliable approach is to compute each metric in its own aggregated CTE at the campaign grain, then join those aggregates to the anchor. This avoids row multiplication across stages (e.g., a user with multiple visits and multiple purchases).

WITH campaign_list AS (  SELECT c.campaign_id, c.campaign_name  FROM campaigns c),visits_by_campaign AS (  SELECT    v.campaign_id,    COUNT(*) AS visits  FROM visits v  GROUP BY v.campaign_id),signups_by_campaign AS (  SELECT    v.campaign_id,    COUNT(DISTINCT s.user_id) AS signups  FROM visits v  JOIN signups s    ON s.session_id = v.session_id  GROUP BY v.campaign_id),purchasers_by_campaign AS (  SELECT    v.campaign_id,    COUNT(DISTINCT o.customer_id) AS purchasers  FROM visits v  JOIN signups s    ON s.session_id = v.session_id  JOIN orders o    ON o.customer_id = s.user_id  GROUP BY v.campaign_id)SELECT  cl.campaign_id,  cl.campaign_name,  COALESCE(vbc.visits, 0) AS visits,  COALESCE(sbc.signups, 0) AS signups,  COALESCE(pbc.purchasers, 0) AS purchasersFROM campaign_list clLEFT JOIN visits_by_campaign vbc  ON vbc.campaign_id = cl.campaign_idLEFT JOIN signups_by_campaign sbc  ON sbc.campaign_id = cl.campaign_idLEFT JOIN purchasers_by_campaign pbc  ON pbc.campaign_id = cl.campaign_idORDER BY cl.campaign_id;

Step-by-step checks:

• Validate each CTE independently (does visits match raw visits count by campaign?).
• Confirm that signups and purchasers are distinct counts as intended.
• Ensure the join path reflects the business definition (e.g., purchases attributed to campaign via session).

Scenario 8: “Customer support performance: tickets with optional SLA policy and agent”

Business question: “List all tickets created last week, show assigned agent name and SLA policy name if present, and flag whether the ticket breached SLA.”

Output contract: one row per ticket; include tickets even if unassigned or missing SLA policy; compute breach flag based on timestamps and SLA target.

Reasoning: Anchor is tickets filtered to last week. Agent and SLA policy are optional lookups. The breach calculation depends on SLA target minutes; if SLA is missing, you need a defined behavior (null breach, or treat as not applicable).

SELECT  t.ticket_id,  t.created_at,  t.closed_at,  a.agent_name,  p.policy_name,  CASE    WHEN p.target_minutes IS NULL THEN NULL    WHEN t.closed_at IS NULL THEN NULL    WHEN t.closed_at > t.created_at + (p.target_minutes * INTERVAL '1 minute') THEN 1    ELSE 0  END AS breached_slaFROM tickets tLEFT JOIN agents a  ON a.agent_id = t.assigned_agent_idLEFT JOIN sla_policies p  ON p.policy_id = t.sla_policy_idWHERE t.created_at >= DATE '2025-01-06'  AND t.created_at <  DATE '2025-01-13';

Practical notes: This pattern highlights a common translation step: optional lookups plus derived logic. Decide upfront what “breach” means when the ticket is open or the policy is missing, and encode that explicitly.

Scenario 9: “Inventory exceptions: products with stock but no active listing”

Business question: “Which products have positive on-hand inventory in warehouses but are not actively listed in the storefront?”

Output contract: one row per product; include only products meeting the exception condition; show on_hand quantity and listing status.

Reasoning: You’re comparing two domains: inventory facts and listings. The anchor can be the set of products with stock (derived from inventory). Listings are optional relative to that set; you then filter for missing or inactive listings.

WITH stock_by_product AS (  SELECT    i.product_id,    SUM(i.on_hand_qty) AS on_hand_qty  FROM inventory i  GROUP BY i.product_id  HAVING SUM(i.on_hand_qty) > 0)SELECT  sbp.product_id,  sbp.on_hand_qty,  l.status AS listing_statusFROM stock_by_product sbpLEFT JOIN listings l  ON l.product_id = sbp.product_id AND l.channel = 'storefront'WHERE l.product_id IS NULL   OR l.status <> 'active'ORDER BY sbp.on_hand_qty DESC;

Step-by-step checks:

• Validate the stock CTE returns only products with positive stock.
• Confirm that products with no listing appear (null listing_status).
• Confirm that products with inactive listings appear with their status.

Debugging and Validation Techniques for Join Pattern Selection

Technique 1: Prove the grain with row counts

After each join, run a quick count and compare it to the expected grain. If your contract is “one row per customer,” then after joining, the row count should not exceed the number of customers unless you intentionally expanded the grain. If it does, you likely joined to a one-to-many table without reducing it first.

Technique 2: Use “presence flags” to confirm optionality

Add boolean indicators like CASE WHEN joined_table.key IS NULL THEN 0 ELSE 1 END to see how many anchor rows have matches. This helps confirm that your join is matching what you think it is (and that keys align).

Technique 3: Validate with small, targeted samples

Pick a handful of known IDs (a customer with no orders, an order with multiple shipments, a campaign with visits but no signups) and run focused queries to verify the joined output aligns with reality. This is often faster than staring at the full query.

Technique 4: Separate “filtering for the metric” from “filtering the output list”

Many questions require filtering which rows contribute to a metric (e.g., only completed orders count as revenue) without filtering the anchor list (e.g., still show all products). Implement metric filters inside the aggregated CTE that computes the metric, not as a global filter that changes which anchor rows survive.

Technique 5: Reconcile totals against known sources

For metric queries, compare totals to a trusted baseline: total revenue for the period, total number of orders, total number of customers. If the join pattern is wrong, totals often drift in recognizable ways (inflated by duplication, deflated by unintended exclusion).