← All workshopsRead What Could the Model Have Known at That Moment?

SQL

Reconstruct Botie’s historical decision inputs

The same upload is invisible at noon and visible at two

A late sensor upload makes yesterday’s model look as though it knew more than the live service could know. Botie must rebuild each decision’s evidence from both clocks.

BOTIE

P7’s event happened at 11:00 and became available at 13:00. D1 is at 12:00 and D3 at 14:00, both with a 24-hour lookback. Which decision may use its value 90?
The inputs we are comparing

Decision identities and UTC cutoffs

The 11:00 reading arrives at 13:00

[
  {
    "decision_id": "D1",
    "asset_id": "P7",
    "cutoff": "2026-01-02 12:00:00"
  },
  {
    "decision_id": "D2",
    "asset_id": "P8",
    "cutoff": "2026-01-02 12:00:00"
  }
]

Two decisions see the same event differently

[
  {
    "decision_id": "D1",
    "asset_id": "P7",
    "cutoff": "2026-01-02 12:00:00"
  },
  {
    "decision_id": "D3",
    "asset_id": "P7",
    "cutoff": "2026-01-02 14:00:00"
  }
]

Measurements with event and availability times

The 11:00 reading arrives at 13:00

[
  {
    "asset_id": "P7",
    "event_at": "2026-01-01 12:00:00",
    "available_at": "2026-01-01 12:05:00",
    "value": 10
  },
  {
    "asset_id": "P7",
    "event_at": "2026-01-02 10:00:00",
    "available_at": "2026-01-02 10:05:00",
    "value": 30
  },
  {
    "asset_id": "P7",
    "event_at": "2026-01-02 11:00:00",
    "available_at": "2026-01-02 13:00:00",
    "value": 90
  },
  {
    "asset_id": "P7",
    "event_at": "2026-01-02 12:00:00",
    "available_at": "2026-01-02 12:00:00",
    "value": 100
  }
]

Two decisions see the same event differently

[
  {
    "asset_id": "P7",
    "event_at": "2026-01-02 11:00:00",
    "available_at": "2026-01-02 13:00:00",
    "value": 90
  }
]

Lookback hours

Same in both cases

24
Result for the 11:00 reading arrives at 13:00
[
  {
    "decision_id": "D1",
    "feature_mean": 20,
    "n": 2
  },
  {
    "decision_id": "D2",
    "feature_mean": null,
    "n": 0
  }
]
Choose an explanation to test
New cutoffs reveal a late upload and an empty decision

This check uses the pieces from “Summarise evidence without dropping empty decisions”.

BOTIE

Use a two-hour lookback for decisions at noon and 14:00 on P2, plus a noon decision on P4. The 11:00 P2 value is uploaded at 13:00. The 12:00 P2 value is available at 12:00. Apply both time rules separately for each decision.
Lookback hours
2
Decision identities and UTC cutoffs
[
  {
    "decision_id": "E3",
    "asset_id": "P4",
    "cutoff": "2026-03-01 12:00:00"
  },
  {
    "decision_id": "E2",
    "asset_id": "P2",
    "cutoff": "2026-03-01 14:00:00"
  },
  {
    "decision_id": "E1",
    "asset_id": "P2",
    "cutoff": "2026-03-01 12:00:00"
  }
]
Measurements with event and availability times
[
  {
    "asset_id": "P2",
    "event_at": "2026-03-01 10:00:00",
    "available_at": "2026-03-01 10:01:00",
    "value": 6
  },
  {
    "asset_id": "P2",
    "event_at": "2026-03-01 11:00:00",
    "available_at": "2026-03-01 13:00:00",
    "value": 30
  },
  {
    "asset_id": "P2",
    "event_at": "2026-03-01 12:00:00",
    "available_at": "2026-03-01 12:00:00",
    "value": 14
  },
  {
    "asset_id": "P4",
    "event_at": "2026-03-01 11:00:00",
    "available_at": "2026-03-01 13:00:00",
    "value": 90
  }
]

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.

What historical evidence would be lost if a later correction overwrote the original value, and why does this feature query still need a separate model-evaluation split?

Preserve information boundaries and value history. Historical features must represent what the deployed decision system could actually have consumed.

The fixture has immutable nonmissing numeric values and unique decision IDs. Revision selection, label creation and model evaluation require additional rules taught in the lesson.

Work through the SQL challenges →
Continue the courseWhat Does One Equipment Record Promise? →
← Is That Temperature a Number or Just Text?