SQL traps · Chapter 5 of 16

Integer division

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 zero rate on purpose

    Write repro.sql. It runs in a fresh, empty database where engagement.sql has just made the table posts and the view post_engagement. Add one post with 50 likes and 200 views. End with a SELECT that gives one value: the rate that post_engagement shows for that post.

    The check runs your file twice, each time in a fresh database: beside the original engagement.sql it must give 0 (no engagement at all), and beside a fixed engagement.sql it must give 25. So it has to really read the view: a file that only says SELECT 0 fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    engagement.sql: the dashboard shows real engagement

    Fix the view post_engagement in engagement.sql. For each post, rate is the share of its views that became likes, as a percentage rounded to 2 decimals: 50 likes from 200 views is 25.00, 1 like from 3 views is 33.33, 2 likes from 3 views is 66.67, and a post every viewer liked is 100.00. A post with 0 views has a NULL rate, with no error. Every post is listed once.

    Keep the table as it is, and keep the view's name and its columns: post_id, and rate as a numeric. The check uses its own posts, and a rate that loses its decimals (33.00 for 1 of 3) is as wrong as the bug.

  3. Task 3

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

    Write a SQL test file, engagement.test.sql, that would have caught this bug before the dashboard reached the investors. 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 engagement.sql has just made the table: beside the original engagement.sql at least one test must fail; beside the fixed engagement.sql every test must pass and nothing may raise an error; beside a hotfix that casts to numeric after the division at least one test must fail; beside a hotfix that multiplies by 100 before dividing at least one test must fail.

    So add posts whose rates you know, at least one of them not a whole number (1 like from 3 views is 33.33), and test the exact rates. Run it with node run.mjs engagement.test.sql.

What you will learn

  • repro.sql: a zero rate on purpose
  • engagement.sql: the dashboard shows real engagement
  • engagement.test.sql: a test that would have caught it

More in Everyday query traps

Build "Integer division" 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.