← All workshopsRead Where Did the Quiet Day Go?

SQL

Restore the missing days to Botie’s report

BOTIE

Append each event’s UTC day, then count rows in each day group into n. Do not invent groups for missing days yet.
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

Append each event’s UTC day, then count rows in each day group into n. Do not invent groups for missing days yet.

A report over events alone cannot show a day with no recorded event.

Inspect the component calculation
Summarise each group(
  Rows: Read the UTC calendar day(
    Timestamped rows: Recorded UTC events
    Timestamp field: occurred_at
    Day field: day
  )
  Group field: day
  Value field: day
  Aggregate: count rows
  Result field: n
)
Examples and results
Recorded UTC events
[
  {
    "occurred_at": "2026-01-01 08:00:00"
  },
  {
    "occurred_at": "2026-01-01 23:59:59"
  },
  {
    "occurred_at": "2026-01-03 00:00:00"
  }
]
Required result
[
  {
    "day": "2026-01-01",
    "n": 2
  },
  {
    "day": "2026-01-03",
    "n": 1
  }
]
Recorded UTC events
[]
Required result
[]
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 accepts valid fixed-format UTC timestamps. It does not infer site timezones or distinguish real quiet days from ingestion outages. 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 new calendar includes quiet days and an outside event

This check uses the pieces from “Attach observed counts to every requested day”.

BOTIE

Report March 1 through 3 in calendar order. An event on March 4 must not add an extra report day. March 1 and March 3 remain visible despite having no events.
Requested report days
[
  {
    "day": "2026-03-03"
  },
  {
    "day": "2026-03-01"
  },
  {
    "day": "2026-03-02"
  }
]
Recorded UTC events
[
  {
    "occurred_at": "2026-03-02 00:00:00"
  },
  {
    "occurred_at": "2026-03-02 23:59:59"
  },
  {
    "occurred_at": "2026-03-04 00:00:00"
  }
]

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.

What additional evidence would distinguish a quiet day from a day when the ingestion service failed?

Make the time convention, interval boundary and observation coverage explicit in time-based reports.

The workshop accepts valid fixed-format UTC timestamps. It does not infer site timezones or distinguish real quiet days from ingestion outages.

Work through the SQL challenges →
Continue the courseWhy Did a Pump Search Return the Wrong Equipment? →
← Which Readings Belong in This Rolling Total?