← All workshopsRead Which Pumps Appear on Either List but Are Still Unfinished?

SQL

Combine reports into unfinished assignments

BOTIE

Project asset_id from both report sources, append the rows, and exclude NULL IDs. Leave repeated known IDs visible at this stage.
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

Project asset_id from both report sources, append the rows, and exclude NULL IDs. Leave repeated known IDs visible at this stage.

Repeated reports and completed work should not produce repeated assignments.

Inspect the component calculation
Check for a missing value(
  Rows: Append compatible result rows(
    First result: Choose table columns(
      Rows: Sensor alert records
      Columns, separated by commas: asset_id
    )
    Second result: Choose table columns(
      Rows: Technician request records
      Columns, separated by commas: asset_id
    )
  )
  Field: asset_id
  Keep: is not null
)
Examples and results
Sensor alert records
[
  {
    "asset_id": "P7"
  },
  {
    "asset_id": "P7"
  },
  {
    "asset_id": "P8"
  }
]
Technician request records
[
  {
    "asset_id": "P8"
  },
  {
    "asset_id": "P9"
  }
]
Required result
[
  {
    "asset_id": "P7"
  },
  {
    "asset_id": "P7"
  },
  {
    "asset_id": "P8"
  },
  {
    "asset_id": "P8"
  },
  {
    "asset_id": "P9"
  }
]
Sensor alert records
[
  {
    "asset_id": "P2"
  }
]
Technician request records
[]
Required result
[
  {
    "asset_id": "P2"
  }
]
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 records use consistent text keys. More general SQL type affinity and collation behaviour is outside this graph. 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.

Completed work removes one shared report from a new batch

This check uses the pieces from “Subtract the completed assignments”.

BOTIE

Combine the known asset IDs, subtract completed IDs and retain one unfinished assignment per asset. The request list repeats P4; completed also contains a pump absent from either report.
Sensor alert records
[
  {
    "asset_id": "P6"
  },
  {
    "asset_id": "P2"
  },
  {
    "asset_id": null
  }
]
Technician request records
[
  {
    "asset_id": "P2"
  },
  {
    "asset_id": "P4"
  },
  {
    "asset_id": "P4"
  }
]
Completed asset records
[
  {
    "asset_id": "P2"
  },
  {
    "asset_id": "P9"
  }
]

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.

How would adding a report_source column change which rows count as duplicates?

Before choosing UNION or a difference, decide whether the result represents events, entities or assignments.

The records use consistent text keys. More general SQL type affinity and collation behaviour is outside this graph.

Work through the SQL challenges →
Continue the courseCan We Read the Query in the Order We Explain the Job? →
← Which Pumps Still Have No Completed Visit?