Chapter 7 · 6 levels

Subqueries and CTEs

Build queries in steps: scalar subqueries, EXISTS, CTEs with WITH, pre-aggregation to avoid fan-out, deduplication and a first retention analysis.

Start

Some questions cannot be answered in one pass. “Products priced above average” needs the average first. “Customer lifetime value” needs revenue per order, then per customer. “Repeat rate” needs each customer’s first order before you can look at what came after it.

Subqueries and common table expressions (CTEs) let you build an answer in named steps, each one a small query you can run and check on its own. That is how analysts write the long queries behind dashboards, and how interviewers expect you to structure a multi-step problem. The boss level is a first-order and repeat-purchase analysis.

Levels

  1. Products priced above average

    from Jessica Turner, Merchandising Manager

    subquery, scalar subquery, avg

    Code

  2. Customers who returned something

    from Rachel Adams, Customer Support Lead

    exists, in subquery, not in null, semi join

    Code

  3. Customer lifetime value

    from Lauren Hayes, Email Marketing Manager

    cte, with, multi step, average order value, lifetime value

    Code

  4. Shipping and merchandise revenue per channel

    from Brian Mitchell, Finance Analyst

    cte, fan out, pre aggregation, left join, coalesce

    Code

  5. Find duplicate customer records

    from Tyler Morgan, Junior Data Engineer

    duplicates, having, normalization, data quality

    Code

  6. Boss: first orders and repeat purchases

    from Megan Collins, Head of Sales

    cte, min, exists, retention, cohort

    Boss · 3 steps