SQL traps · Chapter 4 of 16
Join fan-out doubles the sum
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: likes paid three times, on purpose
Write repro.sql. It runs in a fresh, empty database where payouts.sql has just made the tables users, posts and comments and the view creator_payouts. Add one creator, give them one post with likes_count 10, and add three comments to that post. End with a SELECT that gives one value: the likes that creator_payouts shows for the creator.
The check runs your file twice, each time in a fresh database: beside the original payouts.sql it must give 30 (each like paid once per comment), and beside a fixed payouts.sql it must give 10. So it has to really read the view: a file that only says SELECT 30 fails the second run. Run it with node run.mjs in the terminal.
- Task 2
payouts.sql: the Creator Fund pays each like once
Fix the view creator_payouts in payouts.sql. For each member who has at least one post: likes is the sum of likes_count over their posts, each post counted once however many comments it has; comments is the number of comments on their posts; payout_rupees is 2 rupees a like plus 5 rupees a comment. Two posts with the same likes_count both count. A member with no posts is not listed, as before.
Keep the three tables as they are, and keep the view's name and its columns: username, likes, comments, payout_rupees. The check uses its own creators, posts and comments, and a fix that pays anyone less than they earned is as wrong as the bug.
- Task 3
payouts.test.sql: a test that would have caught it
Write a SQL test file, payouts.test.sql, that would have caught this bug before the Fund paid out. 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 payouts.sql has just made the tables: beside the original payouts.sql at least one test must fail; beside the fixed payouts.sql every test must pass and nothing may raise an error; beside a hotfix that sums only distinct likes counts at least one test must fail.
So add a creator with two posts that have the same likes_count, at least one of them with several comments, and test their likes (each post once), and their comments and payout too. Run it with node run.mjs payouts.test.sql.
What you will learn
- repro.sql: likes paid three times, on purpose
- payouts.sql: the Creator Fund pays each like once
- payouts.test.sql: a test that would have caught it
More in Everyday query traps
Build "Join fan-out doubles the sum" 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.