WindowFunctions-QueryA2-AggLag.sql

WITH prep AS (
   SELECT *
        , LAG(accident_ts) OVER (PARTITION BY severity ORDER BY accident_ts) AS previous_accident_ts
        , DATEDIFF(HOUR, previous_accident_ts, accident_ts)                  AS time_between_accidents
   FROM temp.CAMBRIDGESHIRE_ACCIDENTS
)
SELECT severity
     , AVG(time_between_accidents) AS avg_time_between_accidents_by_severity
FROM prep
GROUP BY severity