SELECT
EXTRACT(YEAR FROM STRPTIME("date", '%B %-d, %Y')) "year",
COUNT(*) "crashes",
SUM(CAST(NULLIF(REGEXP_EXTRACT("fatalities", '([0-9]+).*', 1), '') AS INT)) "fatalities"
FROM @kaggle.luiscfrancisco_plane_crashes_dataset.accident_details_compiled
GROUP BY "year"
HAVING "year" < 2024
ORDER BY "year" ASC