← All workshopsRead Which Readings Belong in This Rolling Total?

SQL

Move a frame through each pump’s readings

BOTIE

Sort usage records by asset_id, measured_at and event_id ascending. Keep amount and all identity fields intact.
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

Sort usage records by asset_id, measured_at and event_id ascending. Keep amount and all identity fields intact.

Botie needs a total over recent observations without mixing pumps or assuming regular sampling.

Inspect the component calculation
Order rows by named fields(
  Rows: Usage observations
  Order, for example minutes DESC, inspection_id ASC: asset_id ASC, measured_at ASC, event_id ASC
)
Examples and results
Usage observations
[
  {
    "event_id": 1,
    "asset_id": "P7",
    "measured_at": "2026-01-01 08:00:00",
    "amount": 2
  },
  {
    "event_id": 2,
    "asset_id": "P7",
    "measured_at": "2026-01-01 08:00:00",
    "amount": 3
  },
  {
    "event_id": 3,
    "asset_id": "P7",
    "measured_at": "2026-01-03 08:00:00",
    "amount": 7
  },
  {
    "event_id": 4,
    "asset_id": "P8",
    "measured_at": "2026-01-01 08:00:00",
    "amount": 10
  }
]
Required result
[
  {
    "event_id": 1,
    "asset_id": "P7",
    "measured_at": "2026-01-01 08:00:00",
    "amount": 2
  },
  {
    "event_id": 2,
    "asset_id": "P7",
    "measured_at": "2026-01-01 08:00:00",
    "amount": 3
  },
  {
    "event_id": 3,
    "asset_id": "P7",
    "measured_at": "2026-01-03 08:00:00",
    "amount": 7
  },
  {
    "event_id": 4,
    "asset_id": "P8",
    "measured_at": "2026-01-01 08:00:00",
    "amount": 10
  }
]
Usage observations
[
  {
    "event_id": 1,
    "asset_id": "P2",
    "measured_at": "2026-01-01 08:00:00",
    "amount": 2
  },
  {
    "event_id": 2,
    "asset_id": "P2",
    "measured_at": "2026-01-01 09:00:00",
    "amount": 3
  },
  {
    "event_id": 3,
    "asset_id": "P2",
    "measured_at": "2026-01-01 10:00:00",
    "amount": 7
  }
]
Required result
[
  {
    "event_id": 1,
    "asset_id": "P2",
    "measured_at": "2026-01-01 08:00:00",
    "amount": 2
  },
  {
    "event_id": 2,
    "asset_id": "P2",
    "measured_at": "2026-01-01 09:00:00",
    "amount": 3
  },
  {
    "event_id": 3,
    "asset_id": "P2",
    "measured_at": "2026-01-01 10:00:00",
    "amount": 7
  }
]
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

This component implements a ROWS sum. It does not claim to implement RANGE, GROUPS or SQL query planning. 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.

Interleaved pumps have irregular observations

This check uses the pieces from “Move the requested row frame through each pump”.

BOTIE

Use one preceding row per asset. Sort the input by asset, time and event ID first. A long gap does not remove the previous observation from a ROWS frame.
Number of preceding observations
1
Usage observations
[
  {
    "event_id": 9,
    "asset_id": "P4",
    "measured_at": "2026-03-20 08:00:00",
    "amount": 8
  },
  {
    "event_id": 5,
    "asset_id": "P2",
    "measured_at": "2026-03-01 08:00:00",
    "amount": 3
  },
  {
    "event_id": 8,
    "asset_id": "P4",
    "measured_at": "2026-03-01 08:00:00",
    "amount": null
  },
  {
    "event_id": 6,
    "asset_id": "P2",
    "measured_at": "2026-03-01 08:00:00",
    "amount": 4
  }
]

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.

Would moving one observation a week later change a two-row frame? What would need to change for a two-day question?

Specify whether a rolling calculation counts observations, peer groups or elapsed time before choosing its frame.

This component implements a ROWS sum. It does not claim to implement RANGE, GROUPS or SQL query planning.

Work through the SQL challenges →
Continue the courseWhere Did the Quiet Day Go? →
← Keep Every Reading While Looking at Its Neighbours