Which Inspections Took Longer Than the Usual Visit?

Botie needs the inspections that took longer than the average recorded visit. We could calculate the average, copy it into another query, and remember to repeat both steps when a new visit arrives. That gives us a value that can become stale as soon as the records change.

Instead, let the comparison obtain its value from another query. We will first inspect that small result on its own, then connect it to the inspection rows.

Calculate a mean from all known durations and feed it into the row comparison.

[
  {
    "inspection_id": 3,
    "minutes": 60
  }
]

Begin with AVG(minutes) over the inspection table. AVG ignores NULL values, so this is the average of recorded durations. If every duration is missing, that result is NULL.

The inner query still has a shape: one column and one row when an aggregate without GROUP BY produces this result. Knowing that shape matters before placing it where a single value belongs.

A scalar means one value. It does not mean that the database forgot where the value came from.

CREATE TABLE inspections(inspection_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, minutes INTEGER);
INSERT INTO inspections VALUES (1,'P7',20),(2,'P7',40),(3,'P8',60),(4,'P9',NULL);

SELECT AVG(minutes) AS mean_minutes FROM inspections;

A scalar subquery can appear on the right of the greater-than comparison. The outer query keeps each inspection whose own minutes exceed the supplied mean.

The independent inner query does not refer to the current outer row, so its meaning is the same for every inspection. This is a useful way to read the expression; the optimiser decides the physical execution plan.

Describe the dependency rather than claiming that the engine must literally execute the inner query once for every row.

Parentheses identify the inner SELECT. Qualify the outer columns with an alias when another occurrence of the same table appears nearby. The final ORDER BY gives the report a reproducible inspection order.

The strict greater-than comparison excludes a visit exactly at the mean. A NULL duration also does not pass it, because that comparison is unknown.

Choose the comparison boundary from the question. Greater than and greater than or equal to select different visits.

CREATE TABLE inspections(inspection_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, minutes INTEGER);
INSERT INTO inspections VALUES (1,'P7',20),(2,'P7',40),(3,'P8',60),(4,'P9',NULL);

SELECT i.inspection_id, i.minutes
FROM inspections AS i
WHERE i.minutes > (SELECT AVG(minutes) FROM inspections)
ORDER BY i.inspection_id;

Suppose pumps have different routine workloads. Comparing each visit with its own asset’s recorded mean asks a different question. The inner WHERE can connect its asset_id to the outer alias.

The same alias must not accidentally refer only to the inner table. Writing inner_visit.asset_id = i.asset_id makes the dependency explicit. Read it as “for this outer inspection, average the visits with this asset ID.”

A correlated subquery depends on an outer value. It does not itself guarantee an efficient implementation.

CREATE TABLE inspections(inspection_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, minutes INTEGER);
INSERT INTO inspections VALUES (1,'P7',20),(2,'P7',40),(3,'P8',60),(4,'P9',NULL);

SELECT i.inspection_id, i.asset_id
FROM inspections AS i
WHERE i.minutes > (
  SELECT AVG(v.minutes) FROM inspections AS v
  WHERE v.asset_id = i.asset_id
)
ORDER BY i.inspection_id;

A plain SELECT of minutes can return several rows. SQLite uses the first returned row when such a query is used as a scalar; other engines can reject a multiple-row scalar result. Neither behaviour establishes that the chosen row is the one the task requires.

Use an aggregate when the task asks for an aggregate. Use a unique key for a unique lookup. If the task asks for the latest row, specify its ordering and tie rule before LIMIT 1. A limit added only to suppress a cardinality problem hides the question rather than answering it.

The workshop’s one-row reader deliberately rejects multiple rows. It teaches the shape requirement without relying on SQLite’s permissive first-row behaviour.

Sometimes the question is simply whether an inspection exists. Returning a duration is then unnecessary, and missing durations make the result harder to interpret. The next lesson will use EXISTS for that question.

For an average comparison, keep a test where all values are NULL and another where every known duration is equal. Both should produce no unusually long visits under the strict rule.

An empty report can be correct. Check its cause before replacing NULL or loosening the threshold.