Get the time between thunderstorms with LAG()

Window functions let you reference values in other rows, like the previous row! This means you can subtract the day in the previous row to get the time between thunderstorms.

 SELECT 
   type,
   day,
   day - lag(day) OVER(PARTITION BY type ORDER BY day ASC) as days_since_prev
 FROM weather
 ORDER BY day ASC

Let's go through that query one step at a time:

1 FROM weather

type day
rain 9
thunderstorm 11
rain 13
rain 21
thunderstorm 22
rain 30
thunderstorm 36
rain 38
thunderstorm 41
rain 48

2 PARTITION BY type

type day
rain 9
rain 13
rain 21
rain 30
rain 38
rain 48
type day
thunderstorm 11
thunderstorm 22
thunderstorm 36
thunderstorm 41

3 ORDER BY day ASC

In this case the rows already look ordered, but you should always use an ORDER BY if you expect a specific order

type day
rain 9
rain 13
rain 21
rain 30
rain 38
rain 48
type day
thunderstorm 11
thunderstorm 22
thunderstorm 36
thunderstorm 41

4 SELECT type, day, day - lag(day) OVER(PARTITION BY type ORDER BY day ASC) as days_since_prev

type day days_since_prev
rain 9
rain 13 4
rain 21 8
rain 30 9
rain 38 8
rain 48 10
thunderstorm 11
thunderstorm 22 11
thunderstorm 36 14
thunderstorm 41 5

5 ORDER BY day ASC

type day days_since_prev
rain 9
thunderstorm 11
rain 13 4
rain 21 8
thunderstorm 22 11
rain 30 9
thunderstorm 36 14
rain 38 8
thunderstorm 41 5
rain 48 10