Skip to Content
A stick figure smiling

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!
Image of a comic. To read the full HTML alt text, click "read the transcript".
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
  1. FROM baby_log
event hour
feeding 1
cough 1
diaper 3
feeding 4
diaper 5
diaper 5
feeding 7
cough 7
  1. `WHERE event IN (‘diaper’, ‘feeding’)
event hour
feeding 1
diaper 3
feeding 4
diaper 5
diaper 5
feeding 7
  1. OVER (PARTITION BY event ORDER BY hour ASC) (this ORDER BY only 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)

  1. SELECT type, 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
  1. 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