SQL traps · Chapter 9 of 16

BETWEEN and the last day

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

  • Part: Time
  • 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: the 30th lost on purpose

    Write repro.sql. It runs in a fresh, empty database where signups.sql has just made the table users and the function signups. Add three members who joined on 1, 15 and 30 September 2026, the one on the 30th at 18:00 in the evening. End with a SELECT that gives one value: what signups gives for 2026-09-01 to 2026-09-30.

    The check runs your file twice, each time in a fresh database: beside the original signups.sql it must give 2 (the member who joined on the 30th is not counted), and beside a fixed signups.sql it must give 3. So it has to really ask the function: a file that only says SELECT 2 fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    signups.sql: the whole last day counts

    Fix the function signups in signups.sql so both days, from_day and to_day, count whole: every member who joined from midnight at the start of from_day until just before midnight at the end of to_day. A member who joined at 2026-09-30 18:00 or at 23:59:59.5 counts for September; one who joined at exactly 2026-10-01 00:00:00 counts for October only, never for September; one who joined at 2026-09-01 00:00:00 counts for September. One day on its own, from the 30th to the 30th, counts that whole day. Days start and end at midnight in India's time, the server's.

    Keep the table users as it is, and keep the function's name, its arguments (from_day date, to_day date) and its bigint result. The check uses its own members, and a fix that adds a day to the end of BETWEEN counts the member who joined at midnight on 1 October in September too.

  3. Task 3

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

    Write a SQL test file, signups.test.sql, that would have caught this bug before the report shipped. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file three times, each in a fresh database where a version of signups.sql has just made the table and the function: beside the original signups.sql at least one test must fail; beside the fixed signups.sql every test must pass and nothing may raise an error; beside a hotfix that adds a day to the end of BETWEEN at least one test must fail.

    So add members at the edges, then test both ends of the month: a member who joined late on the last day counts for that month, and one who joined at exactly midnight on the next day does not. Run it with node run.mjs signups.test.sql.

What you will learn

  • repro.sql: the 30th lost on purpose
  • signups.sql: the whole last day counts
  • signups.test.sql: a test that would have caught it

More in Time

Build "BETWEEN and the last day" 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.