Chapter 11 · 8 levels

Reading and fixing queries

Debug queries you inherit: join fan-out, the wrong GROUP BY grain, WHERE vs HAVING, integer division, date boundaries, time zones and NOT IN with NULLs.

Start

On the job you write fewer queries from scratch than you think. Most weeks you inherit them: a dashboard query from someone who left, a script a colleague pasted into a ticket, a report that “has always worked” until somebody notices the numbers do not add up. Every level in this chapter starts from one of those queries. It runs without an error, and it is wrong.

Read a broken query top-down, one clause at a time: what does FROM and each JOIN make one row mean (the grain), which rows does WHERE drop, what does GROUP BY collapse, and is each filter applied to rows or to groups. Then check row counts against a number you trust, and follow one customer, order or day through the query by hand. The bugs here are the ones that reach real reports most often, and spotting them quickly is what interviewers test with “what is wrong with this query?”

Levels

  1. Shipping revenue that grew after a join

    from Laura Bennett, Head of Finance

    debugging, fan out, grain, pre aggregate, cte

    Code

  2. A daily chart stuck at one order

    from Kevin Brooks, Operations Manager

    debugging, group by, grain, timestamp, cast

    Code

  3. The high-value list that was too long

    from Megan Collins, Head of Sales

    debugging, where, having, aggregate, average order value

    Code

  4. Return rates that are all whole numbers

    from Jessica Turner, Merchandising Manager

    debugging, integer division, rates, round, left join

    Code

  5. December is short by eleven orders

    from Brian Mitchell, Finance Analyst

    debugging, between, timestamp, date range, half open range

    Code

  6. Black Friday in the ad platform's time zone

    from Jordan Reyes, Head of Marketing

    debugging, time zones, utc, timestamp, interval

    Code

  7. Nobody has ever skipped the website?

    from Ashley Rivera, Web Content Lead

    debugging, not in, nulls, not exists, anti join

    Code

  8. Boss: the inherited KPI pack

    from Priya Shah, Head of Data

    debugging, fan out, between, grain, integer division, having

    Boss · 3 steps

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.