← All workshopsRead Find One Pump’s Readings Without Checking Every Pump

SQL

Find the asset region in a composite ordering

BOTIE

Connect reading records and index keys to Arrange records by index keys. Inspect how equal assets become adjacent before the time key orders them.
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

Connect reading records and index keys to Arrange records by index keys. Inspect how equal assets become adjacent before the time key orders them.

The table contains measurements from several pumps.

Inspect the component calculation
Arrange records by index keys(
  Records: Reading identities and key values
  Ordered key names: Ordered index columns
)
Examples and results
Reading identities and key values
[
  {
    "id": "R1",
    "asset": "P7",
    "day": 1
  },
  {
    "id": "R2",
    "asset": "P8",
    "day": 2
  },
  {
    "id": "R3",
    "asset": "P7",
    "day": 3
  },
  {
    "id": "R4",
    "asset": "P8",
    "day": 4
  }
]
Ordered index columns
  1. 0asset
  2. 1day
Required result
[
  {
    "id": "R1",
    "asset": "P7",
    "day": 1
  },
  {
    "id": "R3",
    "asset": "P7",
    "day": 3
  },
  {
    "id": "R2",
    "asset": "P8",
    "day": 2
  },
  {
    "id": "R4",
    "asset": "P8",
    "day": 4
  }
]
Reading identities and key values
[
  {
    "id": "R1",
    "asset": "P7",
    "day": 1
  },
  {
    "id": "R2",
    "asset": "P8",
    "day": 2
  },
  {
    "id": "R3",
    "asset": "P7",
    "day": 3
  },
  {
    "id": "R4",
    "asset": "P8",
    "day": 4
  }
]
Ordered index columns
  1. 0day
  2. 1asset
Required result
[
  {
    "id": "R1",
    "asset": "P7",
    "day": 1
  },
  {
    "id": "R2",
    "asset": "P8",
    "day": 2
  },
  {
    "id": "R3",
    "asset": "P7",
    "day": 3
  },
  {
    "id": "R4",
    "asset": "P8",
    "day": 4
  }
]
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 record ordering illustrates index geometry. It does not model B-tree cost, optimiser choices or real I/O. 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 two-column equality prefix

This check uses the pieces from “Follow the leading composite keys”.

BOTIE

The index keys are asset then day. Find P2 at day 3 from the unsorted records and predict the candidate IDs.
Reading identities and key values
[
  {
    "id": "R9",
    "asset": "P2",
    "day": 4
  },
  {
    "id": "R7",
    "asset": "P2",
    "day": 3
  },
  {
    "id": "R8",
    "asset": "P3",
    "day": 3
  }
]
Ordered index columns
  1. 0asset
  2. 1day
Leading equality keys
{
  "asset": "P2",
  "day": 3
}

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 might scanning the whole table still be cheaper when almost every row matches?

An available index is a candidate route. The optimiser and workload determine whether it is useful.

This record ordering illustrates index geometry. It does not model B-tree cost, optimiser choices or real I/O.

Work through the SQL challenges →
Continue the courseWhy Does a Short Query Repeat So Much Work? →
← What If Someone Changes the Job While Botie Is Reading It?