免費 SQL 練習：為何 SUM(DISTINCT) 無法修正被放大的付款總額

Free SQL practice, with the full data and solution below. No account, paid course, cloud service, or API key is needed. This is a SQLite exercise using synthetic payment data, with a Python standard-library runner if you do not have the SQLite CLI.

Two distinct capture events are each worth 1000 USD cents. A report should show 2000 cents. After a join, one query reports 4000; adding DISTINCT reports 1000. Both are wrong.

I maintain a free, MIT-licensed fintech data engineering roadmap. This article works through the class of bug explored in its C07 scenario: independent detail tables multiply one another before aggregation.

First, define the report's grain

We want one row for each payment and currency with at least one capture:

• gross_minor: sum of capture amounts.
• net_minor: captures minus fees in the same currency.
• attempt_count: attempts at the payment grain, shared across that payment's currencies.

For this synthetic exercise, a missing fee match means zero fees, and a missing attempt match means zero attempts. That is an explicit fixture contract, not a general rule that unknown monetary data should become zero. Fee-only payments are outside the report's population.

Set up the data

Save this block as setup.sql. Run it once in a fresh database.

CREATE TABLE captures (
  id TEXT PRIMARY KEY, payment TEXT, currency TEXT, amount INTEGER
);
CREATE TABLE fees (
  id TEXT PRIMARY KEY, payment TEXT, currency TEXT, amount INTEGER
);
CREATE TABLE attempts (id TEXT PRIMARY KEY, payment TEXT);

INSERT INTO captures VALUES
  ('c1', 'p1', 'USD', 1000),
  ('c2', 'p1', 'USD', 1000),
  ('c3', 'p1', 'EUR', 500),
  ('c4', 'p2', 'USD', 700);
INSERT INTO fees VALUES
  ('f1', 'p1', 'USD', 50),
  ('f2', 'p1', 'EUR', 20);
INSERT INTO attempts VALUES ('a1', 'p1'), ('a2', 'p1');

c1 and c2 have different event IDs and the same amount. Neither is a duplicate event. p1 has both USD and EUR captures. p2 has a capture but no matching fee or attempt.

Before running anything, calculate p1 / USD by hand: 1000 + 1000 = 2000 gross; subtract the 50-cent fee to get 1950 net; there are two attempts.

The query that looks plausible

SELECT c.payment, c.currency,
       SUM(c.amount) AS gross_minor,
       SUM(c.amount) - COALESCE(SUM(f.amount), 0) AS net_minor,
       SUM(DISTINCT c.amount) AS distinct_amount_shortcut,
       COUNT(a.id) AS attempt_count
FROM captures AS c
LEFT JOIN fees AS f
  ON c.payment = f.payment AND c.currency = f.currency
LEFT JOIN attempts AS a ON c.payment = a.payment
GROUP BY c.payment, c.currency
ORDER BY c.payment, c.currency;

For p1 / USD, its output is:

There are two captures, one USD fee, and two attempts. Their joins create four rows. Each capture appears twice; the same fee appears four times. Summing those joined rows inflates gross and fee totals independently.

SUM(DISTINCT c.amount) removes repeated values. The two valid 1000-cent captures collapse into a single value, so it returns 1000. It does not deduplicate by event ID. This follows SQLite's aggregate DISTINCT behavior.

COUNT(DISTINCT a.id) could repair this particular attempt count, but it would not repair the monetary sums. The report needs a consistent grain for each independent measure.

Aggregate each input before joining

Save this corrected query as report.sql.

WITH capture_totals AS (
  SELECT payment, currency, SUM(amount) AS gross_minor
  FROM captures
  GROUP BY payment, currency
), fee_totals AS (
  SELECT payment, currency, SUM(amount) AS fee_minor
  FROM fees
  GROUP BY payment, currency
), attempt_totals AS (
  SELECT payment, COUNT(*) AS attempt_count
  FROM attempts
  GROUP BY payment
)
SELECT c.payment, c.currency, c.gross_minor,
       c.gross_minor - COALESCE(f.fee_minor, 0) AS net_minor,
       COALESCE(a.attempt_count, 0) AS attempt_count
FROM capture_totals AS c
LEFT JOIN fee_totals AS f
  ON c.payment = f.payment AND c.currency = f.currency
LEFT JOIN attempt_totals AS a ON c.payment = a.payment
ORDER BY c.payment, c.currency;

The independently specified expected output, also checked by executing the query in SQLite, is:

Each capture and fee CTE has one row per payment/currency. The attempts CTE has one row per payment. The final joins therefore do not multiply these input aggregates.

LEFT JOIN retains p2. Joining fees on both payment and currency prevents an EUR fee from reducing a USD total. SQLite documents the relevant FROM and JOIN processing.

The two attempts appear on both p1 currency rows because the contract measures attempts per payment. Do not sum that column across currencies and report four attempts. Measures with different grains need different aggregation rules.

Run it with Python, without extra packages

If Python includes sqlite3, save the setup and corrected query under the filenames above, then save this as run_report.py in the same directory:

import sqlite3
from pathlib import Path

with sqlite3.connect(":memory:") as db:
    db.executescript(Path("setup.sql").read_text(encoding="utf-8"))
    query = Path("report.sql").read_text(encoding="utf-8")
    for row in db.execute(query):
        print(row)

Run python run_report.py, or py -3 run_report.py on Windows if needed. The in-memory database starts clean each time.

Try breaking your fix

Add a second USD fee with a different ID but the same 50-cent amount. The correct p1 / USD result should become 2000 gross and 1900 net, with two attempts. A DISTINCT-on-fee-amount shortcut would fail that check too.

Then add a third attempt. Gross and net should remain 2000 and 1900; only attempt_count should become three. These changes test that unrelated detail rows cannot change the monetary total.

Pre-aggregation fixes fan-out; it does not establish that the source events are correct or that settlement agrees. Duplicate delivery and conflicting event identities need separate ingestion checks.

Continue with the free practice repository

The C07 task, hints, cases, and reference extend this into a runnable repair exercise. After cloning the repository:

python challenges/run.py init C07
python challenges/run.py grade C07

The starter intentionally contains a defective query. Edit work/challenges/C07.py, then grade it again. The full repository has 100 English lessons, 40 foundation exercises, and 8 scenario labs, freely available on GitHub. Core practice uses Python 3.12+ and SQLite without paid infrastructure.

Disclosure: I maintain the linked repository. Its first edition and this article were prepared with AI assistance using Codex. All data here is synthetic, and these are teaching examples.

Which extra case would you use to catch a report that accidentally multiplies financial amounts?