SQL traps · Chapter 3 of 16

A WHERE that undoes a LEFT JOIN

Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Everyday query traps
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    repro.sql: a quiet member vanishes on purpose

    Write repro.sql. It runs in a fresh, empty database where activity.sql has just made the tables users and posts and the function monthly_activity(month date). Add two members. Give the first a post in October 2026, and the second a post in September 2026 only. End with a SELECT that gives one value: how many members monthly_activity lists for October 2026, the month that starts on 2026-10-01.

    The check runs your file twice, each time in a fresh database: beside the original activity.sql it must give 1 (the quiet member is missing), and beside a fixed activity.sql it must give 2. So it has to really call the function: a file that only says SELECT 1 fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    activity.sql: quiet members are back in the report

    Fix the function monthly_activity in activity.sql. For a month, it must list every member exactly once, with the number of posts they made in that month: 0 for a member who posted only in other months, 0 for a member who has never posted. A month runs from its first day at midnight up to, not including, the first day of the next month, so a post at midnight on the 1st counts and a post at midnight on the next month's 1st does not.

    Keep the two tables as they are, and keep the function's name, its parameter (month date) and its columns: username, posts. The check uses its own members, posts and months, and a report that counts other months' posts is as wrong as the bug.

  3. Task 3

    activity.test.sql: a test that would have caught it

    Write a SQL test file, activity.test.sql, that would have caught this bug before the report went into the deck. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file four times, each in a fresh database where a version of activity.sql has just made the tables: beside the original activity.sql at least one test must fail; beside the fixed activity.sql every test must pass and nothing may raise an error; beside a hotfix that keeps the rows where the post id is NULL at least one test must fail; beside a hotfix that drops the month filter at least one test must fail.

    So add a member who posted only in another month, a member who never posted, and a member who posted in the month and in another one, and test every side: the quiet members are listed with 0, and the busy member's count has only that month's posts. Run it with node run.mjs activity.test.sql.

What you will learn

  • repro.sql: a quiet member vanishes on purpose
  • activity.sql: quiet members are back in the report
  • activity.test.sql: a test that would have caught it

More in Everyday query traps

Build "A WHERE that undoes a LEFT JOIN" yourself

A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.