Loadingone moment…
Sign in to run and submit your work
Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.
or
CODE WORKSPACE
Driver phones report online intervals, and they are not tidy: intervals overlap, one can sit entirely inside another, and one can end exactly when the next begins. Supply needs continuous online sessions per driver and total online minutes with overlaps counted once.
Return one row per driver, ordered by driver_id.
Result columns · in this order
driver_id | The driver. |
sessions | Continuous online blocks after merging. |
online_minutes | Total online minutes, overlaps counted once. |
How to approach it
A row starts a new session only if it begins after everything seen so far has ended.
Sample input
| shift_id | driver_id | started_at | ended_at |
|---|---|---|---|
| 1 | dr1 | 2026-03-02 06:00:00 | 2026-03-02 09:00:00 |
| 2 | dr1 | 2026-03-02 08:30:00 | 2026-03-02 11:00:00 |
| 3 | dr1 | 2026-03-02 09:15:00 | 2026-03-02 10:00:00 |
| 4 | dr1 | 2026-03-02 14:00:00 | 2026-03-02 16:00:00 |
| 5 | dr2 | 2026-03-02 07:00:00 | 2026-03-02 10:00:00 |
| 6 | dr2 | 2026-03-02 10:00:00 | 2026-03-02 12:30:00 |
| 7 | dr2 | 2026-03-02 18:00:00 | 2026-03-02 20:00:00 |
| 8 | dr3 | 2026-03-02 05:00:00 | 2026-03-02 13:00:00 |
| 9 | dr3 | 2026-03-02 06:00:00 | 2026-03-02 07:00:00 |
| 10 | dr3 | 2026-03-02 12:00:00 | 2026-03-02 12:30:00 |
| 11 | dr4 | 2026-03-02 06:30:00 | 2026-03-02 08:00:00 |
| 12 | dr4 | 2026-03-02 11:00:00 | 2026-03-02 12:00:00 |
| 13 | dr4 | 2026-03-02 19:00:00 | 2026-03-02 21:30:00 |
| 14 | dr5 | 2026-03-02 09:00:00 | 2026-03-02 11:00:00 |
| 15 | dr5 | 2026-03-02 10:30:00 | 2026-03-02 12:00:00 |
| 16 | dr5 | 2026-03-02 11:45:00 | 2026-03-02 14:00:00 |
| 17 | dr5 | 2026-03-02 16:00:00 | 2026-03-02 17:00:00 |
| 18 | dr5 | 2026-03-02 16:30:00 | 2026-03-02 18:00:00 |
| 19 | dr2 | 2026-03-02 12:30:00 | 2026-03-02 13:00:00 |
| 20 | dr4 | 2026-03-02 07:30:00 | 2026-03-02 09:00:00 |
20 rows — scroll inside the table to see them all.
Expected output
| driver_id | sessions | online_minutes |
|---|---|---|
| dr1 | 2 | 420 |
| dr2 | 2 | 480 |
| dr3 | 1 | 480 |
| dr4 | 3 | 360 |
| dr5 | 2 | 420 |
5 rows — all rows shown.
Constraints
dr3's shifts 9 and 10 sit inside shift 8.sessions is the number of continuous blocks; online_minutes is the total length of those blocks, as a whole number.driver_id.Worked example
dr1 reports 06:00–09:00, 08:30–11:00, 09:15–10:00 and 14:00–16:00. The first three merge into one block from 06:00 to 11:00, and the fourth is separate: 2 sessions and 420 minutes.
Adding the raw durations gives 495 minutes across 4 sessions — 75 minutes of double-counted overlap. Comparing each row only with the row before it is the subtler bug: shift 3 starts at 09:15, after shift 2's start, so a naive LAG on the previous end would open a new session even though 09:15 is still inside the block that runs to 11:00.
What this tests
Interval merging — the hard form of gaps and islands. The state that decides whether a row continues a block is the running maximum end so far, not the previous row's end, and only a running window can carry it.
Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.
This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.