Loadingone moment…
Sign in to run and submit your work
Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.
or
CODE WORKSPACE
The ingestion pipeline guarantees at-least-once delivery, so meta_event_log can hold the same event several times: exact redeliveries, later corrections with a higher version, and tombstones where is_deleted is 1. Analytics needs the current state of the stream.
Return one row per event_name, ordered by event_name.
Result columns · in this order
event_name | Kind of event. |
live_events | Distinct events currently live. |
corrected_events | How many of those were corrected at least once. |
How to approach it
Pick the winner per event_id first. Only then ask whether that winner is a tombstone.
Sample input
| row_id | event_id | event_name | version | is_deleted | ingested_at |
|---|---|---|---|---|---|
| 1 | e1 | post_created | 1 | 0 | 2026-03-01 10:00:00 |
| 2 | e1 | post_created | 1 | 0 | 2026-03-01 10:00:04 |
| 3 | e2 | comment_added | 1 | 0 | 2026-03-01 10:05:00 |
| 4 | e2 | comment_added | 2 | 0 | 2026-03-01 11:20:00 |
| 5 | e2 | comment_added | 3 | 0 | 2026-03-02 09:10:00 |
| 6 | e3 | post_created | 1 | 0 | 2026-03-01 12:00:00 |
| 7 | e3 | post_created | 2 | 1 | 2026-03-03 08:00:00 |
| 8 | e4 | reaction_added | 1 | 0 | 2026-03-01 13:00:00 |
| 9 | e4 | reaction_added | 2 | 1 | 2026-03-02 13:00:00 |
| 10 | e4 | reaction_added | 3 | 0 | 2026-03-04 07:30:00 |
| 11 | e5 | comment_added | 2 | 0 | 2026-03-02 15:00:00 |
| 12 | e5 | comment_added | 1 | 0 | 2026-03-05 06:00:00 |
| 13 | e6 | post_created | 1 | 0 | 2026-03-02 16:00:00 |
| 14 | e7 | reaction_added | 1 | 0 | 2026-03-03 09:00:00 |
| 15 | e7 | reaction_added | 2 | 0 | 2026-03-03 09:00:30 |
| 16 | e8 | comment_added | 1 | 1 | 2026-03-03 10:00:00 |
| 17 | e9 | post_created | 1 | 0 | 2026-03-04 11:00:00 |
| 18 | e9 | post_created | 1 | 0 | 2026-03-04 11:00:02 |
| 19 | e10 | reaction_added | 1 | 0 | 2026-03-04 12:00:00 |
| 20 | e10 | reaction_added | 2 | 1 | 2026-03-05 12:00:00 |
| 21 | e11 | comment_added | 1 | 0 | 2026-03-05 13:00:00 |
| 22 | e12 | post_created | 2 | 0 | 2026-03-05 14:00:00 |
22 rows — scroll inside the table to see them all.
Expected output
| event_name | live_events | corrected_events |
|---|---|---|
| comment_added | 3 | 2 |
| post_created | 4 | 1 |
| reaction_added | 2 | 2 |
3 rows — all rows shown.
Constraints
event_id. The current row for it is the one with the highest version.e5 version 1 landed three days after version 2, so ingested_at cannot be used to pick the winner.e4 was tombstoned at version 2 and re-created at version 3, so it is live.live_events counts current, non-deleted events. corrected_events counts how many of those are on a version above 1.event_name.Worked example
e4 has three rows: created at version 1, tombstoned at version 2, re-created at version 3. The current version is 3 and it is not deleted, so e4 is live.
Filter WHERE is_deleted = 0 first and the tombstone is thrown away before it can be compared, so e3 — which really was retracted at version 2 — survives on its version 1 row. The filter that looks like it removes deleted events actually resurrects them. SELECT DISTINCT event_id has the same shape of bug: it keeps an arbitrary version, which for e5 is a coin flip between 1 and 2.
What this tests
Idempotent deduplication: picking one row per key by an ordering the producer controls rather than by arrival, and the ordering of filter and rank — applying a tombstone before the rank silently undeletes exactly the wrong rows.
Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.
This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.