← Problems18. Gaps and islands in device uptimeHardSQL
00:00 / 20:00

Gaps and islands in device uptime

Hard·Acceptance —·Asked at Microsoft, Uber

Devices emit a heartbeat every minute. Collapse consecutive heartbeats into uptime intervals: a gap of more than 5 minutes starts a new interval.

Input schema

heartbeats device_id bigint beat_at timestamp

Example

device_id interval_start interval_end beats 3 2024-06-01 00:00:00 2024-06-01 00:04:00 5 3 2024-06-01 00:20:00 2024-06-01 00:21:00 2

Constraints

  • A single heartbeat is a valid interval where start equals end
  • Duplicate heartbeats at the same timestamp count once
  • Order by device_id, then interval_start
  • Up to 500,000,000 heartbeats

Topics

window functionsaggregation

Similar problems

Community-reported interview topic. Not an official company question and no affiliation is implied.

solution.sql
Loading editor…
Draft not saved yet · Spaces 4 · UTF-8 · ⌘↵ run, ⌘⇧↵ submit
Nothing run yet

Run against the public tests, or submit to score against all of them.