Here's a preview from my zine, Become a SELECT Star!! If you want to see more comics like this, sign up for my saturday comics newsletter or browse more comics!
get the zine!
read the transcript!
This query finds the time since a baby’s last feeding/diaper change.
SELECT event, hour,
hour - LAG(hour) OVER(PARTITION BY event ORDER BY hour ASC)
AS time_since_last
FROM baby_log
WHERE event in ('feeding', 'diaper')
ORDER BY hour ASC
FROM baby_log
| event | hour |
|---|---|
| feeding | 1 |
| cough | 1 |
| diaper | 3 |
| feeding | 4 |
| diaper | 5 |
| diaper | 5 |
| feeding | 7 |
| cough | 7 |
- `WHERE event IN (‘diaper’, ‘feeding’)
| event | hour |
|---|---|
| feeding | 1 |
| diaper | 3 |
| feeding | 4 |
| diaper | 5 |
| diaper | 5 |
| feeding | 7 |
- OVER (PARTITION BY event ORDER BY hour ASC)
(this
ORDER BYonly affects the windows, not the query output)
(There’s a diagram of the table from step 2. There are arrows pointing to two smaller tables that break out only the lines where the event is “feeding”, and only the lines where the event is “diaper”, respectively)
SELECTtype, hour, hour-LAG(hour)
| event | hour | time_since_last |
|---|---|---|
| feeding | 1 | NULL (LAG() is NULL for the first row in the window) |
| feeding | 4 | 3 |
| feeding | 7 | 3 |
| diaper | 3 | NULL |
| diaper | 5 | 2 |
| diaper | 5 | 0 |
ORDER BY hour ASC
| event | hour | time_since_last |
|---|---|---|
| feeding | 1 | NULL |
| diaper | 3 | NULL |
| feeding | 4 | 3 |
| diaper | 5 | 2 |
| diaper | 5 | 0 |
| feeding | 7 | 3 |