Module 5 — Integration & Interview Mastery

Why "combining" is the real skill

Every SQL interview question looks novel, but almost none of them are testing a technique you haven't seen. They're testing whether you can decompose a messy business question into a sequence of the five tools you already have, in the right order. "Find the second-highest-paid employee per department who joined in the last year and has never been late on a project" isn't a new concept — it's a JOIN to bring tables together, a WHERE to filter dates, a window function to rank within department, and a wrapper to pick rn = 2. The novelty is entirely in the assembly.

The good news: there are only a handful of recurring shapes these combinations take. Once you can recognize the shape, the specific column names stop mattering. This module builds that pattern recognition directly.

The query-building mental model

Before writing a single line of SQL in an interview, run the question through this sequence out loud. It's the same five questions every time.

Shared schema for this module

Every example below uses this schema so nothing needs to be re-learned. It extends the customers / orders tables from earlier modules with order_items, products, and regions so joins have somewhere real to go.

Revenue for an order is always quantity * unit_price summed across its order_items — this is deliberate, so every worked problem forces you to aggregate before you can talk about "order revenue" at all.

CTE + JOIN: aggregate first, join second

The most common production shape: pre-aggregate a many-rows table down to the target grain inside a CTE, then join it to a dimension table. This avoids fan-out (duplicated rows from a one-to-many join inflating a SUM).

WITH order_revenue AS (
    SELECT
        o.order_id,
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.order_id, o.customer_id
),
customer_revenue AS (
    SELECT customer_id, SUM(order_total) AS lifetime_value
    FROM order_revenue
    GROUP BY customer_id
)
SELECT c.customer_name, r.region_name, cr.lifetime_value
FROM customer_revenue cr
JOIN customers c ON c.customer_id = cr.customer_id
JOIN regions r   ON r.region_id  = c.region_id
ORDER BY cr.lifetime_value DESC;

CTE + Window: Top-N per group, the canonical shape

This exact pattern — CTE to establish revenue at the right grain, window function to rank within a partition, outer query to filter the rank — is the single most-repeated shape in SQL interviews. Master this once and a huge fraction of "top N per group" questions become typing exercises.

WITH region_customer_revenue AS (
    SELECT
        c.region_id,
        c.customer_id,
        c.customer_name,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM customers c
    JOIN orders o      ON o.customer_id = c.customer_id AND o.status = 'completed'
    JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY c.region_id, c.customer_id, c.customer_name
),
ranked AS (
    SELECT
        *,
        DENSE_RANK() OVER (
            PARTITION BY region_id ORDER BY revenue DESC
        ) AS rnk
    FROM region_customer_revenue
)
SELECT region_id, customer_name, revenue
FROM ranked
WHERE rnk <= 3
ORDER BY region_id, rnk;

If two customers in APAC are tied for 3rd-highest revenue, DENSE_RANK lets both through as "rank 3" — you might return 4 rows for "top 3." ROW_NUMBER arbitrarily picks one. Ask the interviewer which behavior they want; naming this tradeoff unprompted is a strong signal.

Subquery vs JOIN vs EXISTS: how to actually choose

These three overlap heavily and interviewers love asking "could you also write this as a JOIN?" Know the decision rule cold.

-- "Customers who have never placed an order" — EXISTS is the safe, fast choice
SELECT c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- The equivalent LEFT JOIN version — useful when you ALSO need order columns
SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

A correlated subquery in SELECT or WHERE (referencing the outer row) is usually a sign you could rewrite as a window function or a join — and a window function is almost always faster, because it avoids re-running the subquery once per outer row.

Multi-CTE pipelines: chain, don't nest

Once a question needs three or more transformation stages, chain CTEs in a straight line — each one reads only from the previous — instead of nesting subqueries three deep. It reads like a script, and it's how you'd actually build the equivalent pipeline in dbt or PySpark.

WITH completed_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
),
order_totals AS (
    SELECT co.order_id, co.customer_id, co.order_date,
           SUM(oi.quantity * oi.unit_price) AS order_total
    FROM completed_orders co
    JOIN order_items oi ON oi.order_id = co.order_id
    GROUP BY co.order_id, co.customer_id, co.order_date
),
customer_monthly AS (
    SELECT customer_id,
           DATE_TRUNC('month', order_date) AS order_month,
           SUM(order_total) AS monthly_total
    FROM order_totals
    GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
with_trend AS (
    SELECT *,
           LAG(monthly_total) OVER (
               PARTITION BY customer_id ORDER BY order_month
           ) AS prev_month_total
    FROM customer_monthly
)
SELECT customer_id, order_month, monthly_total,
       monthly_total - prev_month_total AS mom_change
FROM with_trend
ORDER BY customer_id, order_month;

Correlated subquery vs window function, side by side

A classic "why would I use A over B" pairing. Both answer "how does this row compare to its group," but they differ in cost and flexibility.

-- Correlated subquery: re-runs once per outer row
SELECT o.order_id, o.customer_id, ot.order_total,
       (SELECT AVG(ot2.order_total)
        FROM order_totals ot2
        WHERE ot2.customer_id = ot.customer_id) AS customer_avg
FROM order_totals ot;

-- Window function: one pass over the data
SELECT order_id, customer_id, order_total,
       AVG(order_total) OVER (PARTITION BY customer_id) AS customer_avg
FROM order_totals;

Both return identical results. The window version is computed in a single pass by the engine, while the correlated subquery conceptually re-executes for every outer row — on large tables this is a real, measurable difference, and it's why "rewrite this correlated subquery as a window function" is a common follow-up question.

GROUP BY & HAVING

WHERE filters rows before grouping; HAVING filters groups after aggregation. This is a logical-order fact, not a style choice — you cannot reference an aggregate like SUM(order_total) in WHERE because it doesn't exist yet at that stage.

SELECT customer_id, COUNT(*) AS order_count, SUM(order_total) AS total_spend
FROM order_totals
WHERE order_date >= '2024-01-01'      -- row-level filter, before grouping
GROUP BY customer_id
HAVING SUM(order_total) > 500;        -- group-level filter, after aggregation

CASE WHEN: conditional logic inline

CASE WHEN is how you bucket, pivot, or branch logic without leaving SQL. It's evaluated top to bottom and stops at the first match, like an if/elif chain.

SELECT
    customer_id,
    total_spend,
    CASE
        WHEN total_spend >= 1000 THEN 'VIP'
        WHEN total_spend >= 300  THEN 'Regular'
        ELSE 'New'
    END AS customer_tier
FROM customer_revenue;

-- Conditional aggregation: CASE WHEN inside SUM/COUNT is the standard "pivot" pattern
SELECT
    region_id,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
GROUP BY region_id;

Date, timestamp & string functions

Names vary by engine (Snowflake, Postgres, MySQL, BigQuery) but the concepts are universal. Know the concept and look up the exact function name for whatever dialect the interview specifies.

-- "Customers acquired in each of the last 6 months" — date bucketing + window in one query
SELECT
    DATE_TRUNC('month', signup_date) AS cohort_month,
    COUNT(*) AS new_customers
FROM customers
WHERE signup_date >= DATEADD(month, -6, CURRENT_DATE)
GROUP BY DATE_TRUNC('month', signup_date)
ORDER BY cohort_month;

UNION / UNION ALL, NULL handling, EXISTS

UNION deduplicates and is slower (it has to sort/hash to find duplicates); UNION ALL keeps everything and is faster. Default to UNION ALL unless you specifically need deduplication — this is a frequent "do you know the performance implication" check.

SELECT customer_id, 'order' AS source, order_date AS event_date FROM orders
UNION ALL
SELECT customer_id, 'signup' AS source, signup_date AS event_date FROM customers;

COALESCE(a, b, c) returns the first non-NULL argument — the standard way to supply defaults. NULLIF(a, b) returns NULL if a = b, otherwise a — most commonly used to avoid divide-by-zero errors.

SELECT
    customer_id,
    COALESCE(phone, email, 'no contact on file') AS contact,
    total_spend / NULLIF(order_count, 0) AS avg_order_value
FROM customer_summary;

Basic performance awareness

• Filter before you join or aggregate — a WHERE that narrows rows early reduces the work every later stage has to do.
• Don't join tables you don't need — every unused join is wasted I/O and a fan-out risk; only join what the output or a filter actually requires.
• Prefer window functions over correlated subqueries for "compare row to its group" logic — one pass beats one-subquery-execution-per-row.
• Aggregate to the smallest necessary grain before joining — join a pre-aggregated CTE to a small dimension table rather than joining raw fact tables and aggregating after.
• UNION ALL over UNION unless you need deduplication.
• Avoid functions on indexed/join columns in WHERE (e.g. WHERE YEAR(order_date) = 2024) — this can prevent the engine from using an index; prefer a range: WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'.

Q1: Top spender per region, tie-safe Hard

Question: "For each region, find the customer with the highest total completed-order revenue. If there's a tie, include all tied customers."

WITH revenue_by_customer AS (
    SELECT
        c.region_id,
        c.customer_id,
        c.customer_name,
        SUM(oi.quantity * oi.unit_price) AS total_revenue
    FROM customers c
    JOIN orders o       ON o.customer_id = c.customer_id AND o.status = 'completed'
    JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY c.region_id, c.customer_id, c.customer_name
),
ranked AS (
    SELECT *,
           RANK() OVER (PARTITION BY region_id ORDER BY total_revenue DESC) AS rnk
    FROM revenue_by_customer
)
SELECT region_id, customer_name, total_revenue
FROM ranked
WHERE rnk = 1;

Why RANK, not ROW_NUMBER: the question explicitly says "include all tied customers" — RANK gives tied top rows the same rank 1, so WHERE rnk = 1 naturally returns every tie. ROW_NUMBER would arbitrarily drop all but one.

Q2: Customers with no orders in the last 90 days Hard

Question: "Find customers who placed at least one order historically, but have made no orders in the last 90 days — flag them for a re-engagement campaign."

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
AND NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= DATEADD(day, -90, CURRENT_DATE)
);

Why two EXISTS clauses, not one: the question has two separate conditions — "has ordered before, ever" and "hasn't ordered recently." Collapsing this into a single subquery with a date filter would silently exclude customers who never ordered at all, which is a different segment than the one asked for.

Q3: Month-over-month revenue growth by category Hard

Question: "For each product category, show monthly revenue and the percent change from the previous month."

WITH item_revenue AS (
    SELECT
        p.category,
        DATE_TRUNC('month', o.order_date) AS order_month,
        oi.quantity * oi.unit_price AS line_revenue
    FROM order_items oi
    JOIN orders o    ON o.order_id = oi.order_id AND o.status = 'completed'
    JOIN products p  ON p.product_id = oi.product_id
),
monthly_category AS (
    SELECT category, order_month, SUM(line_revenue) AS revenue
    FROM item_revenue
    GROUP BY category, order_month
),
with_prev AS (
    SELECT *,
           LAG(revenue) OVER (PARTITION BY category ORDER BY order_month) AS prev_revenue
    FROM monthly_category
)
SELECT
    category, order_month, revenue,
    ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS pct_change
FROM with_prev
ORDER BY category, order_month;

Note the layering: join+aggregate to get monthly revenue at the category grain, then apply LAG — you cannot compute LAG(revenue) until revenue itself exists as a column, which is exactly the "build, then rank/compare" shape from the Top-N pattern earlier in this module.

Q4: Repeat purchase rate within 30 days Hard

Question: "Of customers who placed a first order, what percent placed a second order within 30 days of their first?"

WITH numbered_orders AS (
    SELECT
        customer_id, order_id, order_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq
    FROM orders
    WHERE status = 'completed'
),
first_orders AS (
    SELECT customer_id, order_date AS first_order_date
    FROM numbered_orders WHERE order_seq = 1
),
second_orders AS (
    SELECT customer_id, order_date AS second_order_date
    FROM numbered_orders WHERE order_seq = 2
)
SELECT
    ROUND(100.0 * SUM(CASE WHEN DATEDIFF(day, f.first_order_date, s.second_order_date) <= 30
                            THEN 1 ELSE 0 END) / COUNT(*), 1) AS repeat_rate_pct
FROM first_orders f
LEFT JOIN second_orders s ON s.customer_id = f.customer_id;

Why LEFT JOIN: customers with no second order must still count in the denominator (COUNT(*)) — an inner join would silently drop them and inflate the rate. Their DATEDIFF comparison naturally evaluates to NULL/false via the CASE WHEN, contributing 0 to the numerator without special-casing.

Revenue funnel drop-off by stage FAANG

Question: "Users move through stages: viewed → added_to_cart → purchased, logged as events with a timestamp. For each stage, report how many distinct users reached it and the drop-off percentage from the previous stage."

This is a conditional-aggregation problem dressed up as a funnel. Each user's max stage reached determines which later stages they "reached" — model it with MAX(CASE WHEN ...) flags per user, then aggregate.

WITH user_stage_flags AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'viewed'         THEN 1 ELSE 0 END) AS reached_view,
        MAX(CASE WHEN event_type = 'added_to_cart'   THEN 1 ELSE 0 END) AS reached_cart,
        MAX(CASE WHEN event_type = 'purchased'       THEN 1 ELSE 0 END) AS reached_purchase
    FROM funnel_events
    GROUP BY user_id
),
stage_counts AS (
    SELECT
        SUM(reached_view)     AS viewed,
        SUM(reached_cart)     AS added_to_cart,
        SUM(reached_purchase) AS purchased
    FROM user_stage_flags
)
SELECT
    'viewed' AS stage, viewed AS users, NULL AS dropoff_pct FROM stage_counts
UNION ALL
SELECT
    'added_to_cart', added_to_cart,
    ROUND(100.0 * (viewed - added_to_cart) / NULLIF(viewed, 0), 1)
FROM stage_counts
UNION ALL
SELECT
    'purchased', purchased,
    ROUND(100.0 * (added_to_cart - purchased) / NULLIF(added_to_cart, 0), 1)
FROM stage_counts;

What this tests: whether you'll reach for conditional aggregation instead of trying to self-join the events table three times, and whether you know UNION ALL is the clean way to reshape a single wide row of stage counts into a tall stage-by-stage report.

Churn-risk customers, multi-signal FAANG

Question: "Flag customers as churn-risk if: (a) their most recent order is more than 60 days old, AND (b) their order frequency has slowed — their average days-between-orders in the last 3 orders is more than double their historical average."

Break the two conditions into separate CTEs so each stays testable in isolation, then combine with a final join/filter — resist the urge to write this as one giant nested query.

WITH ordered AS (
    SELECT customer_id, order_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn_desc,
           LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_date
    FROM orders WHERE status = 'completed'
),
gaps AS (
    SELECT customer_id, order_date, rn_desc,
           DATEDIFF(day, prev_date, order_date) AS gap_days
    FROM ordered WHERE prev_date IS NOT NULL
),
recent_gap_avg AS (
    SELECT customer_id, AVG(gap_days) AS recent_avg_gap
    FROM gaps WHERE rn_desc <= 3
    GROUP BY customer_id
),
historical_gap_avg AS (
    SELECT customer_id, AVG(gap_days) AS historical_avg_gap
    FROM gaps
    GROUP BY customer_id
),
last_order AS (
    SELECT customer_id, MAX(order_date) AS last_order_date
    FROM orders WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT lo.customer_id
FROM last_order lo
JOIN recent_gap_avg r     ON r.customer_id = lo.customer_id
JOIN historical_gap_avg h ON h.customer_id = lo.customer_id
WHERE DATEDIFF(day, lo.last_order_date, CURRENT_DATE) > 60
  AND r.recent_avg_gap > 2 * h.historical_avg_gap;

SCD Type 2 + dedup + incremental load, combined

Production pipelines chain these same five tools into a repeatable pattern: dedupe a raw landing table, detect what changed since the last load, and write history-preserving rows. Here's the shape end to end.

WITH deduped_source AS (
    -- Step 1: dedup — keep the latest row per natural key from a raw/staging load
    SELECT *
    FROM (
        SELECT s.*,
               ROW_NUMBER() OVER (
                   PARTITION BY customer_id ORDER BY loaded_at DESC
               ) AS rn
        FROM staging_customers s
    ) t
    WHERE rn = 1
),
changed_records AS (
    -- Step 2: incremental — only rows whose tracked attributes differ from the current dim row
    SELECT ds.*
    FROM deduped_source ds
    JOIN dim_customers dc ON dc.customer_id = ds.customer_id AND dc.is_current = TRUE
    WHERE ds.email <> dc.email OR ds.region_id <> dc.region_id
),
new_records AS (
    -- Step 3: brand-new customers with no current dim row at all
    SELECT ds.*
    FROM deduped_source ds
    LEFT JOIN dim_customers dc ON dc.customer_id = ds.customer_id AND dc.is_current = TRUE
    WHERE dc.customer_id IS NULL
)
-- Step 4 (outside this SELECT, in the surrounding pipeline):
--   UPDATE dim_customers SET is_current = FALSE, valid_to = CURRENT_DATE
--     WHERE customer_id IN (SELECT customer_id FROM changed_records);
--   INSERT INTO dim_customers SELECT ... FROM changed_records UNION ALL SELECT ... FROM new_records;
SELECT * FROM changed_records
UNION ALL
SELECT * FROM new_records;

Notice this is the exact same toolbox as the interview problems above — ROW_NUMBER for dedup, a LEFT JOIN to detect absence, a comparison join to detect change, UNION ALL to combine — just aimed at a pipeline instead of a report. This is the real payoff of mastering combinations: interview SQL and production SQL are the same skill wearing different clothes.

A repeatable framework for any SQL interview question

Rapid-fire Q&A

Self-check before you move on