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:
| type | day |
| rain | 9 |
| thunderstorm | 11 |
| rain | 13 |
| rain | 21 |
| thunderstorm | 22 |
| rain | 30 |
| thunderstorm | 36 |
| rain | 38 |
| thunderstorm | 41 |
| rain | 48 |
| type | day |
| rain | 9 |
| rain | 13 |
| rain | 21 |
| rain | 30 |
| rain | 38 |
| rain | 48 |
| type | day |
| thunderstorm | 11 |
| thunderstorm | 22 |
| thunderstorm | 36 |
| thunderstorm | 41 |
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 |
| 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 |
| 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 |