SQL
Keep pumps awaiting a completed visit
An unattributed completion must not hide P9
The technician wants pumps still awaiting a completed visit. Repeated visits and one unattributed record should not change an unrelated pump’s status.
BOTIE
The inputs we are comparing
Registered asset rows
Same in both cases
[
{
"asset_id": "P7"
},
{
"asset_id": "P8"
},
{
"asset_id": "P9"
}
]Visit log
A repeated closed visit and an open visit
[
{
"asset_id": "P7",
"status": "closed"
},
{
"asset_id": "P7",
"status": "closed"
},
{
"asset_id": "P8",
"status": "open"
}
]An unattributed record enters the log
[
{
"asset_id": "P7",
"status": "closed"
},
{
"asset_id": null,
"status": "closed"
},
{
"asset_id": "P8",
"status": "closed"
}
]Result for a repeated closed visit and an open visit
[
{
"asset_id": "P8"
},
{
"asset_id": "P9"
}
]A new registry includes an open visit and an unattributed completion
This check uses the pieces from “Ask whether each registered pump has a qualifying match”.
BOTIE
- Registered asset rows
[ { "asset_id": "P6" }, { "asset_id": "P2" }, { "asset_id": "P4" } ]- Visit log
[ { "asset_id": "P2", "status": "closed" }, { "asset_id": "P2", "status": "closed" }, { "asset_id": "P4", "status": "open" }, { "asset_id": null, "status": "closed" } ]
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 does the unknown visit ID not prove anything about P9, and why are two visits for P7 still one existence answer?
Use presence and absence operations when the question does not require materialising every matching pair.
The operation models an equality-based anti-join over the supplied records. It is not a query planner.
Work through the SQL challenges →