SQL
Keep assets with no loose findings
BOTIE
Examples, experiments and workspace controls
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.
Context for this challenge
Build the filtered left join before grouping. Find the null visit identity on an asset with no qualifying partner.
The supervisor needs a count for every registered asset, including those with no qualifying visits.
Inspect the component calculation
Match rows on a key(
Left rows: Asset register
Right rows: Keep rows passing a comparison(
Rows: Inspection visits
Comparison value: Text comparison value(
Text: loose
)
Field to compare: finding
Comparison: =
)
Left match field: asset_id
Right match field: asset_id
Join: left
Right fields to carry: inspection_id
Prefix for right fields: visit_
)Examples and results
- Asset register
[ { "asset_id": "P1", "site": "north", "kind": "pump" }, { "asset_id": "P2", "site": "north", "kind": "fan" }, { "asset_id": "P3", "site": "south", "kind": "pump" }, { "asset_id": "P4", "site": "south", "kind": "pump" } ]- Inspection visits
[ { "inspection_id": 101, "asset_id": "P1", "minutes": 30, "finding": "loose" }, { "inspection_id": 102, "asset_id": "P1", "minutes": null, "finding": null }, { "inspection_id": 103, "asset_id": "P2", "minutes": 0, "finding": "clear" }, { "inspection_id": 104, "asset_id": "P3", "minutes": 20, "finding": "clear" }, { "inspection_id": 105, "asset_id": "P2", "minutes": 10, "finding": "loose" }, { "inspection_id": 106, "asset_id": "P1", "minutes": 15, "finding": "loose" } ]- Required result
[ { "asset_id": "P1", "site": "north", "kind": "pump", "visit_inspection_id": 101 }, { "asset_id": "P1", "site": "north", "kind": "pump", "visit_inspection_id": 106 }, { "asset_id": "P2", "site": "north", "kind": "fan", "visit_inspection_id": 105 }, { "asset_id": "P3", "site": "south", "kind": "pump", "visit_inspection_id": null }, { "asset_id": "P4", "site": "south", "kind": "pump", "visit_inspection_id": 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 workshop calculates over small record lists to make the relational operations visible. It is not a SQL parser or query planner. The worksheet runs SQLite and is the authority for SQL behaviour, including types and NULL. 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 real qualifying visit has no recorded minutes
This check uses the pieces from “Keep the register while counting qualifying visits”.
BOTIE
- Asset register
[ { "asset_id": "P8" }, { "asset_id": "P9" } ]- Inspection visits
[ { "inspection_id": 8, "asset_id": "P8", "finding": "loose", "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.
Why should the group key come from the left asset register rather than the nullable right side?
Preserve the identity of the population the report promises to include.
The workshop calculates over small record lists to make the relational operations visible. It is not a SQL parser or query planner. The worksheet runs SQLite and is the authority for SQL behaviour, including types and NULL.
Work through the SQL challenges →