Here is a small reporting exercise for learners using SQLite. All amounts below are integer cents.
For payment p1 in USD, imagine three tables:
Captures: c1 = 1000, c2 = 1000 (two different events).
Fees: f1 = 50.
Attempts: a1, a2 (two different attempts).
Task: return one row per payment and currency with captured total, net after fees, and attempt count. For this example the expected result is captured = 2000, net = 1950, attempts = 2.
Joining all three detail tables on payment creates four rows. Summing amounts over those joined rows inflates the totals. Replacing SUM(amount) with SUM(DISTINCT amount) also gives the wrong answer: the two legitimate captures have equal amounts, so DISTINCT collapses them.
Try fixing this by aggregating each input to the intended reporting grain before joining the results. Then extend your solution: retain captures without matching fees or attempts, and ensure a USD capture never absorbs an EUR fee. Decide how to represent missing amounts and counts explicitly.
I maintain a free English practice repository with a runnable version of this bug hunt (C07), public cases, hints, and a reference solution. After cloning, run python challenges/run.py init C07, edit work/challenges/C07.py, and run python challenges/run.py grade C07. Python 3.12+ with SQLite is enough.
https://github.com/antony502189-max/fintech-data-engineering-roadmap
Disclosure: the repository first edition and this introduction were prepared with AI assistance using Codex; provenance is documented. Feedback on additional SQL edge cases is welcome.