Chapter 5 · 6 levels

JOINs

Combine tables: INNER and LEFT joins, finding missing matches, the LEFT JOIN + WHERE trap, fan-out double counting and self-joins.

Start

Real questions rarely live in one table. Revenue is in order_items, the category is in products, the status is in orders, and the customer’s name is in customers. Joins put them together.

They are also where experienced analysts still make expensive mistakes: rows that vanish because a filter turned a LEFT JOIN into an INNER JOIN, or totals that double because one order matched many lines. This chapter teaches the joins and, just as importantly, how to check that a join did what you think. The boss level is a category scorecard with revenue, return rates and top customers.

Levels

  1. Revenue by category

    from Jessica Turner, Merchandising Manager

    inner join, table alias, group by

    Code

  2. Customers who never ordered

    from Lauren Hayes, Email Marketing Manager

    left join, anti join, is null

    Code

  3. App orders per business customer

    from Daniel Foster, Mobile Product Manager

    left join, on vs where, count, zero rows

    Code

  4. Lines per order without double counting

    from Kevin Brooks, Operations Manager

    fan out, one to many, count distinct, grain

    Code

  5. Who reports to whom

    from Nicole Bennett, HR Business Partner

    self join, left join, concatenation

    Code

  6. Boss: category scorecard

    from Megan Collins, Head of Sales

    inner join, left join, multi join, rates, top n

    Boss · 3 steps