Use desktop for interactive SQL sandbox

Duplicate Deliveries: When a Retry Skews Your Average

A CO₂ sensor takes four readings. Your query averages six rows. Which number goes on the dashboard?

Networks are unreliable, so devices retry. If the sensor sends a reading and doesn't hear back in time, it sends it again, even when the first copy got through. The database ends up with one row per delivery, but what you care about is one row per measurement.

This exercise has six rows and one bug, and you can see it in a single AVG().

Adapted with permission from Sensor retries by telemetry.sh. The readings are fictional.

The Data

One sensor reports every 10 seconds. Reading r4 was delivered three times because of retries:

DROP TABLE IF EXISTS sensor_deliveries;

CREATE TABLE sensor_deliveries (
  reading_id   VARCHAR(10) NOT NULL,  -- assigned by the sensor, one per measurement
  measured_s   INT         NOT NULL,  -- second the sensor took the reading
  co2_ppm      INT         NOT NULL,  -- CO2 concentration, parts per million
  delivered_s  INT         NOT NULL   -- second the row arrived at the server
);

INSERT INTO sensor_deliveries (reading_id, measured_s, co2_ppm, delivered_s) VALUES
('r1',  0, 600,  1),
('r2', 10, 650, 11),
('r3', 20, 700, 21),
('r4', 30, 750, 31),
('r4', 30, 750, 32),
('r4', 30, 750, 33);

Before running anything, work it out on paper. The four readings are 600, 650, 700 and 750. What's their average?

Query 1: The Naive Average

SELECT COUNT(*)               AS row_count,
       ROUND(AVG(co2_ppm), 1) AS avg_ppm
FROM sensor_deliveries;

Output:

 row_count | avg_ppm
-----------+---------
         6 |   700.0

AVG() doesn't know that three of those rows are the same measurement. It adds all six values (4200) and divides by six. Reading r4 is counted three times, which pulls the average up toward 750.

The correct answer is (600 + 650 + 700 + 750) / 4 = 675 ppm.

Query 2: Find the Duplicates

Before fixing anything, confirm what's duplicated. A reading should appear once, so any reading_id with more than one row is a retry:

SELECT reading_id,
       COUNT(*) AS deliveries
FROM sensor_deliveries
GROUP BY reading_id
HAVING COUNT(*) > 1;

Output:

 reading_id | deliveries
------------+------------
 r4         |          3

It's good practice to also check that the copies really are identical. If two rows share a reading_id but have different values, you have a data conflict, not a retry:

SELECT reading_id
FROM sensor_deliveries
GROUP BY reading_id
HAVING COUNT(DISTINCT co2_ppm) > 1
    OR COUNT(DISTINCT measured_s) > 1;

No rows means every duplicate is an exact copy, so it's safe to keep any one of them.

The Tempting Fix (Don't)

Your first instinct might be AVG(DISTINCT ...):

SELECT ROUND(AVG(DISTINCT co2_ppm), 1) AS avg_ppm
FROM sensor_deliveries;
 avg_ppm
---------
   675.0

It gives 675, the right number, but for the wrong reason. DISTINCT co2_ppm removes duplicate values, not duplicate readings. It only works here because the four readings happen to have four different values.

Add a fifth reading that also measured 650 ppm:

INSERT INTO sensor_deliveries VALUES ('r5', 40, 650, 41);

Now there are five readings (600, 650, 700, 750, 650) with a true average of 670. AVG(DISTINCT co2_ppm) still says 675, because it drops the second 650 even though it's a separate measurement.

Delete it again before moving on:

DELETE FROM sensor_deliveries WHERE reading_id = 'r5';

Rule of thumb: deduplicate on the thing that identifies a measurement (reading_id), never on the value you're measuring.

Query 3: Deduplicate, Then Average

Collapse the deliveries into one row per reading first, then aggregate:

SELECT COUNT(*)               AS readings,
       ROUND(AVG(co2_ppm), 1) AS avg_ppm
FROM (
  SELECT DISTINCT reading_id, measured_s, co2_ppm
  FROM sensor_deliveries
) AS readings;

Output:

 readings | avg_ppm
----------+---------
        4 |   675.0

delivered_s is left out on purpose. It's the one column that differs between retries, so including it would keep all three r4 rows.

Query 4: Keep the First Delivery with ROW_NUMBER()

SELECT DISTINCT only works when the duplicates are identical in every column you select. A more flexible pattern is to number the deliveries of each reading and keep one:

SELECT COUNT(*)               AS readings,
       ROUND(AVG(co2_ppm), 1) AS avg_ppm
FROM (
  SELECT reading_id,
         co2_ppm,
         ROW_NUMBER() OVER (
           PARTITION BY reading_id
           ORDER BY delivered_s
         ) AS delivery_no
  FROM sensor_deliveries
) AS d
WHERE delivery_no = 1;

Output:

 readings | avg_ppm
----------+---------
        4 |   675.0

PARTITION BY reading_id restarts the count for each reading. ORDER BY delivered_s makes the earliest arrival number 1. You could keep the latest instead with ORDER BY delivered_s DESC, which matters when later copies can carry corrections.

Challenge

For each reading that was retried, show how many extra deliveries it caused and how many seconds passed between the first and last arrival.

Expected output:

 reading_id | extra_deliveries | retry_span_s
------------+------------------+--------------
 r4         |                2 |            2

Hint: group by reading_id, keep only groups with more than one row, and use COUNT(*), MIN() and MAX() on delivered_s.

Two Caveats

  • 675 is the average of the readings, not of the room. It's only a fair room average if the readings are evenly spaced in time and cover the period you care about. Here they're 10 seconds apart, so that holds. With gaps or bursts, you'd need a time-weighted average.
  • Deduplication doesn't fix a bad sensor. Real deployments also check calibration, sensor placement, and missing readings. A clean average from a sensor next to an open window is still wrong.

Real-World Applications

The same delivery-vs-event mismatch shows up whenever a system uses at-least-once delivery:

  • IoT and telemetry: devices retry on flaky connections
  • Payment webhooks: providers resend events until you acknowledge them, so dedupe on the event ID or you'll count a payment twice
  • Message queues: Kafka, SQS and similar systems can redeliver after a consumer crash
  • Analytics events: mobile apps buffer and resend events when they come back online

In each case the fix is the same as here: carry a stable ID from the source, and aggregate after deduplicating on it.

Prevent It at Write Time

If the table should only ever hold one row per reading, enforce it:

ALTER TABLE sensor_deliveries
  ADD CONSTRAINT uq_reading UNIQUE (reading_id);

Inserts can then use INSERT ... ON CONFLICT (reading_id) DO NOTHING (PostgreSQL) so retries are absorbed silently. Run this on a clean copy: on the six-row table above it fails, because r4 is already duplicated. Keep the raw deliveries table if you need to measure retry rates. Otherwise, dedupe on the way in.

SQL Output

Duplicate Deliveries
Public
A sensor reading arrives three times and the average jumps from 675 to 700 ppm. Learn to tell deliveries from measurements and deduplicate with DISTINCT, GROUP BY and ROW_NUMBER().
jrz1977
PostgreSQL
Created Nov 16, 2026
1Notes Notes
2SQL SQL
3Queries SQL
2 SQL 1 Notes
Switch to a SQL tab and press Alt+Enter to run queries
Duplicate Deliveries Updated 11/16/26, 4:07 AM
Notes SQL Queries
Alt+Enter to run