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.

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

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

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