← All workshopsRead Can We Read the Query in the Order We Explain the Job?

SQL

Keep open estimates out of completed totals

BOTIE

Filter status closed, then sum minutes within each asset into total_minutes. Inspect the changed row grain before adding a threshold.
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

Filter status closed, then sum minutes within each asset into total_minutes. Inspect the changed row grain before adding a threshold.

The maintenance report must not count work that has not finished.

Inspect the component calculation
Summarise each group(
  Rows: Keep rows passing a comparison(
    Rows: Recorded jobs
    Comparison value: Set a text value(
      Text: closed
    )
    Field to compare: status
    Comparison: =
  )
  Group field: asset_id
  Value field: minutes
  Aggregate: sum
  Result field: total_minutes
)
Examples and results
Recorded jobs
[
  {
    "asset_id": "P7",
    "status": "closed",
    "minutes": 20
  },
  {
    "asset_id": "P7",
    "status": "closed",
    "minutes": 35
  },
  {
    "asset_id": "P7",
    "status": "open",
    "minutes": 200
  },
  {
    "asset_id": "P8",
    "status": "closed",
    "minutes": 40
  }
]
Required result
[
  {
    "asset_id": "P7",
    "total_minutes": 55
  },
  {
    "asset_id": "P8",
    "total_minutes": 40
  }
]
Recorded jobs
[
  {
    "asset_id": "P7",
    "status": "closed",
    "minutes": 50
  }
]
Required result
[
  {
    "asset_id": "P7",
    "total_minutes": 50
  }
]
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

CTE names and query-plan choices are taught in SQL; the graph shows their logical record transformations. 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.

The budget changes and one total is exactly on it

This check uses the pieces from “Apply the budget to the completed total”.

BOTIE

The threshold is now 30 completed minutes. P2 has 12 and 23 closed minutes, P4 has exactly 30, and P6 has only a large open estimate. Report strictly qualifying totals.
Completed-minute threshold
30
Recorded jobs
[
  {
    "asset_id": "P4",
    "status": "closed",
    "minutes": 30
  },
  {
    "asset_id": "P2",
    "status": "closed",
    "minutes": 12
  },
  {
    "asset_id": "P6",
    "status": "open",
    "minutes": 900
  },
  {
    "asset_id": "P2",
    "status": "closed",
    "minutes": 23
  }
]

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.

Which intermediate result would you inspect if an open estimate appeared in the total?

Name query stages around their row meaning, and use those boundaries to locate errors rather than hiding them in a final expression.

CTE names and query-plan choices are taught in SQL; the graph shows their logical record transformations.

Work through the SQL challenges →
Continue the courseHow Far Does the Failed Supply Line Reach? →
← Which Pumps Appear on Either List but Are Still Unfinished?