Why Did Joining Readings Double the Inspection Total?

Botie adds temperature readings to an inspection report so the team can compare work and sensor coverage. The inspection total increases, even though nobody added an inspection. The query runs without an error.

For one asset, several visits can match several readings. The joined rows now describe visit-reading pairs. Adding a visit duration over those pairs counts the same visit repeatedly. We need to decide the final report’s grain before combining the event streams.

Count visits and readings in separate groups, then join the two summaries onto the asset register.

[
  {
    "asset_id": "P1",
    "visits": 3,
    "readings": 2
  },
  {
    "asset_id": "P2",
    "visits": 2,
    "readings": 1
  },
  {
    "asset_id": "P3",
    "visits": 1,
    "readings": 1
  }
]

Before the join, one inspection row means one visit and one reading row means one observation. After matching both streams only on asset_id, one result row means one visit-reading pair for that asset.

Both keys can be correct and every individual pair can satisfy the join condition. The error appears when we treat that result as though each visit still appears only once. Row counts before and after the join are useful diagnostic evidence.

A successful key comparison does not preserve the original grain automatically.

Write the pairs by hand for a tiny example. A visit is repeated once for every matching reading, and a reading is repeated once for every matching visit. That gives a concrete explanation for an inflated sum or count.

An asset with only one reading can hide the problem during initial testing. Add a second reading for an asset with multiple visits and watch which metrics change despite no change to the inspection log.

The fixture should contain a many-to-many combination, not only one-to-one examples.

SUM(DISTINCT minutes) deduplicates numeric values, not visits. If two legitimate visits both lasted ten minutes, their equal amounts collapse into one value even though both visits should count.

COUNT(DISTINCT inspection_id) can correctly count distinct visits in some joined results, but it does not automatically repair other aggregates or justify the whole join. Decide the report’s required grain and build each measure from its own event identity.

A repeated amount is not evidence of a duplicated event.

First group inspections by asset_id and count their rows. Separately group readings by asset_id and count their rows. Each derived table now has at most one row per asset. Join these summaries to the register.

A subquery in FROM creates a named intermediate result for this purpose. Its alias lets the outer query refer to the count. Later lessons study subqueries and common table expressions more broadly; here the nested query has one specific job: establish the correct grain before the match.

The outer join combines asset-level facts instead of raw event pairs.

CREATE TABLE assets (
  asset_id TEXT PRIMARY KEY NOT NULL,
  site TEXT NOT NULL,
  kind TEXT NOT NULL,
  active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE TABLE inspections (
  inspection_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  minutes INTEGER CHECK (minutes >= 0),
  finding TEXT
);
CREATE TABLE readings (
  reading_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  temperature REAL
);
INSERT INTO assets VALUES
 ('P1','north','pump',1), ('P2','north','fan',1),
 ('P3','south','pump',0), ('P4','south','pump',1);
INSERT INTO inspections VALUES
 (101,'P1',30,'loose'), (102,'P1',NULL,NULL),
 (103,'P2',0,'clear'), (104,'P3',20,'clear'),
 (105,'P2',10,'loose'), (106,'P1',15,'loose');
INSERT INTO readings VALUES
 (1,'P1',70.0), (2,'P1',80.0), (3,'P2',NULL), (4,'P3',65.0);

SELECT asset_id, COUNT(*) AS visits
FROM inspections GROUP BY asset_id ORDER BY asset_id;

Use left joins from the asset register when every asset must appear. An absent summary produces NULL in its count column. In this particular report, COALESCE of that count to zero is justified: there were no source event rows for the asset.

Do not extend that reasoning to an average or sum over unknown measurements without checking its meaning. A missing event-count summary and a summary of unknown durations are different cases.

A zero count can be a deliberate result without pretending an unknown measurement was zero.

The reading count here includes rows with temperature NULL because the question is how many reading records arrived. COUNT(temperature) would count only records carrying a known temperature. Both can be useful, but they answer different questions.

The query contract names visit records and reading records so the denominator is explicit. A later coverage report could include both reading records and known temperatures.

Keep the metric’s event or value population in its name and explanation.

Add a reading without adding a visit. The reading count should change; the visit count should not. Then add a visit with no corresponding new reading. The visit count should change independently.

These are stronger tests than checking one initial table, because they express an invariant of the report. The workshop uses exactly this kind of changed shift to expose raw-event joins that multiply unrelated measures.

Independent source facts should remain independent in the asset-level report.

A many-to-many join can duplicate training examples, change their effective weights and leak information across splits. An apparently better model may simply have seen repeated or future-related observations.

Check unique observation keys and row counts after feature joins. If time matters, define which event can attach to which observation using a time-aware relationship. Matching by asset alone is rarely enough for a prediction-time feature table.

The database returns the pairs you requested. The analyst must ensure those pairs match the learning problem.