← All workshopsRead Which Inspections Took Longer Than the Usual Visit?

SQL

Let the inspection records supply their own benchmark

BOTIE

Average known minutes into one result row, then read mean_minutes as a scalar. Leave an all-unknown average as NULL.
Ready to test
0 passed · 0 failed · 2 not run

An empty floor. Start with the information your machine needs.

Received result

Your answer will arrive here.

Examples, experiments and workspace controls
Live values

Connect a tool's inputs to see what it produces. You can try a branch before connecting it to Result.

Try your own inputs

Use a copy of the example to try your own data. Keep the problem's assumptions in mind, and predict the effect of a change before checking the output. You can also add another branch to compare two ways of doing the calculation.

Start at an output arrow, then click or drag to an input. Drag empty space to pan. Delete removes a selection. Ctrl/Cmd+Z undoes a change.
Context for this challenge

Average known minutes into one result row, then read mean_minutes as a scalar. Leave an all-unknown average as NULL.

A copied average will become stale when the next visit arrives.

Inspect the component calculation
Read the one-row result(
  Subquery result: Summarise all rows(
    Rows: Inspection records
    Value field: minutes
    Aggregate: avg
    Result field: mean_minutes
  )
  Result field: mean_minutes
)
Examples and results
Inspection records
[
  {
    "inspection_id": 1,
    "minutes": 20
  },
  {
    "inspection_id": 2,
    "minutes": 40
  },
  {
    "inspection_id": 3,
    "minutes": 60
  },
  {
    "inspection_id": 4,
    "minutes": null
  }
]
Required result
40
Inspection records
[
  {
    "inspection_id": 7,
    "minutes": null
  }
]
Required result
null
Try a fresh construction

This clears this draft, including its undo history. Earlier successful attempts stay recorded. Build again without opening hints to record an independent attempt.

Stuck? Inspect a worked construction

The tools below form one possible solution. You can assemble it, follow its signals, and undo that change to return to your own construction.

Assumptions and test precision

The visible graph models this calculation. The SQL editor uses SQLite’s actual subquery implementation. Numerical tests allow a difference of 0.001. Lists and structures must also have the right shape.

Your last tested pieces can start the full construction. An existing draft there keeps its own pieces.

A different shift has two visits above its own mean

This check uses the pieces from “Feed that value into the inspection comparison”.

BOTIE

The recorded minutes are 5, 10, 35, 70 and NULL. Recalculate the mean from known values and return inspections strictly above it, in inspection-ID order.
Inspection records
[
  {
    "inspection_id": 12,
    "minutes": 5
  },
  {
    "inspection_id": 10,
    "minutes": 70
  },
  {
    "inspection_id": 11,
    "minutes": 35
  },
  {
    "inspection_id": 14,
    "minutes": 10
  },
  {
    "inspection_id": 13,
    "minutes": null
  }
]

Use a number, text, or an array matching the requested result. For a matrix, put each row in brackets. Saving your prediction runs your current construction on these inputs and checks the earlier examples too.

Build a construction in the workspace above first. This case will test those pieces.

How would a copied average fail after another upload, and what should happen when every duration is unknown?

Before composing queries, state the inner result’s shape and how missing values affect the outer condition.

The visible graph models this calculation. The SQL editor uses SQLite’s actual subquery implementation.

Work through the SQL challenges →
Continue the courseWhich Pumps Still Have No Completed Visit? →
← Why Did Joining Readings Double the Inspection Total?