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.
- 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.
- 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.
- 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.