Practice · lesson 12/13

🏁Mock contest: 6 problems

⏳ ~60 min

A dress rehearsal: 6 new problems you haven't seen in the labs, in the same mix the real contest is likely to have (3 SQL, 3 Python, easy → hard). The real thing is 150 minutes for 6 problems; this one is shorter, so give yourself 60 minutes and practise the strategy as much as the code.

⏱️ Mock timerruns in your browser and survives page switches

60:00

Rules of the rehearsal

0–4 min Read all six statements below before writing any code. Tag each 🟢 / 🟡 / 🔴.

4–30 min Bank the 🟢 ones. Submit the moment the samples pass.

30–55 min 🟡 then 🔴 with a 12-minute time box each. Stuck? Write the simplest version that handles the obvious cases.

55–60 min Stop starting things. Re-read statements for rounding, ordering and empty cases.

No hints, no solutions until the timer ends. In the real contest you won't have them either. Afterwards, open the reference solutions and compare. Check the explanations: they cover the traps each problem was built around.

The SQL problems run on SQLite like the SQL lab. As practice for a MySQL judge, write down in a comment how you'd translate the date functions (date(d, '-1 month') → d - INTERVAL 1 MONTH).

Problem A · SQL · Conversion funnel

easy

Conversion funnel

any order✓ solved

From events, return a single row views, carts, purchases, conversion_pct: the number of distinct users with at least one event of each type, and purchases / views × 100 rounded to 1 decimal.

Problem B · Python · JSON Lines with bad records

easy

JSON Lines with bad records

stdin → stdout✓ solved

stdin holds JSON Lines until EOF. A record is valid if it's a JSON object with a string "type" and a numeric "amount" (int or float, not bool, not a string). Blank lines are ignored. Everything else (broken JSON, arrays, missing or wrong fields) is bad.
Output one line type count total per type sorted by type (total with 2 decimals), then a final line bad N.

Problem C · SQL · Daily active users with a date spine

medium

Daily active users incl. zero days

order matters✓ solved

Return day, active_users for every day from 2026-05-01 to 2026-05-07 inclusive, where active_users = distinct users in logins that day. Days without logins must appear with 0. Order by day.

Problem D · Python · Messy CSV report

medium

Messy CSV → city report

stdin → stdout✓ solved

stdin is a CSV with header order_id,customer,city,amount,status. Fields may be quoted and contain commas ("Tehran, Iran", "1,200.50").

An order_id may appear several times: the last line wins (dedupe before filtering).
Keep orders whose status is completed case-insensitively, ignoring surrounding spaces.
Skip orders with an empty amount.

Print a CSV city,orders,revenue (with that header) sorted by revenue desc, then city asc; revenue with 2 decimals. Quote fields only when needed (the default of Python's csv module).

Problem E · SQL · Month-over-month retention

hard

Month-over-month retention

order matters✓ solved

A customer is active in a month if they have a completed order in it. For every month with activity return month (YYYY-MM), active_customers, and retained = how many of those customers were also active in the previous calendar month. Order by month.

Problem F · Python · Identity stitching

hard

Identity stitching (union-find)

implement group_accounts()✓ solved

group_accounts(accounts): each account is {"id", "email", "phone"} (either may be None). Two accounts belong to the same person if they share an email (compare stripped + lower-cased) or a phone (compare digits only), directly or through a chain of other accounts. Values that are empty after normalising never link anything.
Return the groups as sorted lists of ids, the groups sorted by their smallest id.

Debrief

Score yourself honestly, then fix the habit rather than the bug:

What happened	What to change in the real contest
Wrong number of rows in A or C	You counted rows instead of distinct users, or used `COUNT(*)` after a LEFT JOIN. Say out loud what one output row means before writing SQL.
B or D failed only on one test	An edge case: bools are ints in Python, empty input, dedupe before filtering. Write the edge-case list from the statement first.
E gave too many "retained"	You compared with the previous active month instead of the previous calendar month. Boundary definitions decide hidden tests.
F is missing accounts	Accounts with no identifiers never touched the union-find. Every input entity must appear in the output.
Ran out of time on one problem	The time box failed. Next time, set an actual alarm and move on when it rings.

Solved everything with time to spare? Redo the hardest one in the other language: E in Python (defaultdict(set) of months per customer) or F in SQL (recursive CTE over an edge table). Translating between SQL and Python thinking is exactly what a data engineer does all day.