lesson 50 Advanced product analysis · advanced

Deduplicate event retries

Identify repeated events and keep a consistent record.

the idea

Clients retry when a delivery times out, so one logical event can arrive several times with the same event_id. Keep one delivery per event_id with ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at, delivery_id) = 1, then count logical events.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Find event_ids with retries. Run this. deliveries > 1 means the pipeline delivered the same logical event more than once.the pattern

   example.sql Run

   sql›

   SELECT event_id, COUNT(*) AS deliveries
   FROM events
   GROUP BY event_id
   HAVING COUNT(*) > 1
   LIMIT 10;

2. 2

   Open one retried event. Run this. You see every delivery row for a single event_id—same occurred_at, different delivery_id and received_at.

   step-2.sql Run

   sql›

   SELECT delivery_id, event_id, event_name, occurred_at, received_at
   FROM events
   WHERE event_id = (
     SELECT event_id
     FROM events
     GROUP BY event_id
     HAVING COUNT(*) > 1
     LIMIT 1
   );

3. 3

   Rank deliveries per event. Run this. ROW_NUMBER() ordered by received_at marks the earliest delivery as 1—that is the copy you keep for analytics.

   step-3.sql Run

   sql›

   SELECT
     event_id,
     delivery_id,
     received_at,
     ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at, delivery_id) AS delivery_rank
   FROM events
   WHERE event_id IN (
     SELECT event_id
     FROM events
     WHERE occurred_at >= '2025-05-01' AND occurred_at < '2025-05-02'
     GROUP BY event_id
     HAVING COUNT(*) > 1
   )
   ORDER BY event_id, delivery_rank;

common mistake

COUNT(*) on the raw table counts deliveries, not events. Retries inflate every metric built on it.