← All workshopsRead Which Columns Answer the Question?

SQL

List equipment categories by site

BOTIE

Select only site and kind. Inspect the repeated pairs before removing any rows.
Ready to test
0 passed · 0 failed · 1 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

Select only site and kind. Inspect the repeated pairs before removing any rows.

The stores team needs unique site-kind pairs, not an asset-by-asset list.

Inspect the component calculation
Choose table columns(
  Rows: Asset register
  Columns, separated by commas: site,kind
)
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"
  }
]
Required result
[
  {
    "site": "north",
    "kind": "pump"
  },
  {
    "site": "north",
    "kind": "fan"
  },
  {
    "site": "south",
    "kind": "pump"
  },
  {
    "site": "south",
    "kind": "pump"
  }
]
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.

Several assets form one pair while a new kind adds another

This check uses the pieces from “Build the site-category report”.

BOTIE

West has two fans and one pump. Predict the unique site-kind pairs, in the requested order.
Asset register
[
  {
    "asset_id": "P9",
    "site": "west",
    "kind": "pump"
  },
  {
    "asset_id": "P8",
    "site": "west",
    "kind": "fan"
  },
  {
    "asset_id": "P7",
    "site": "west",
    "kind": "fan"
  }
]

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 would adding asset_id to this result keep the two south pumps separate?

Choose the output fields before deciding which complete rows are duplicates.

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 →
Continue the courseWhich Inspection Records Need Our Attention? →
← What Does One Row Actually Describe?