for every accident, show how much time (in hours) has passed since the last time the road became Wet/Damp.

WindowFunctions-QueryC-TimeSinceEvent.sql

WITH relevant_set AS (
   SELECT *
        -- has_started_raining means: (previous!=Wet AND current=Wet)
        , LAG(road_cond) OVER (ORDER BY accident_ts, police_ref)        AS prev_road_cond
        , prev_road_cond != '2. Wet/Damp' AND road_cond = '2. Wet/Damp' AS has_started_raining
        , CASE WHEN has_started_raining THEN accident_ts END            AS time_started_raining
   FROM temp.CAMBRIDGESHIRE_ACCIDENTS
)

SELECT accident_ts
     , police_ref
     , LAST_VALUE(time_started_raining)
                  IGNORE NULLS
                     OVER (ORDER BY accident_ts ROWS UNBOUNDED PRECEDING) AS last_it_started_raining_ts
     , DATEDIFF(HOUR, last_it_started_raining_ts, accident_ts)            AS elapsed_since_last_rained_hh
     
FROM relevant_set
ORDER BY 1, 2