Australian Road Trauma Hospitalisation Analysis (SQL)

SQL analysis of 11 years (2011–2021) of Australian road crash hospitalisation data, published by BITRE (Bureau of Infrastructure and Transport Research Economics). Built in MySQL, covering schema design, data cleaning, and progressively more advanced SQL from basic aggregation up to window functions.

Status: in progress. This first table (national monthly data) is complete and verified.

Data source

BITRE hospitalisation injury publication national, monthly counts of road-crash-related hospitalisations, broken down by cause of injury, age group, sex, road user type, counterparty, and remoteness area. ~118,000 rows before cleaning.

Project structure

database.sql         -- schema creation + data load
table_cleaning.sql   -- verification, integrity checks and cleanup
eda.sql              -- analysis queries (aggregation to CTEs to window functions)

What's in each file

database.sql

Creates the road_trauma database and national_monthly_facts table, then loads the source CSV using LOAD DATA LOCAL INFILE. Uses NULLIF() on the count columns so blank cells in the source data become proper NULLs instead of being silently coerced to 0 as missing data and zero are not the same thing, and this preserves that distinction.

table_cleaning.sql

Verifies the load rather than assuming it worked. While validating row counts against the expected total, I found a discrepancy of exactly 128 rows. I traced it to blank trailing rows introduced during the Excel-to-CSV export (every field blank or zero) and removed them. Final row count: 117,919, matching the source data exactly.

eda.sql

Nine queries, building in complexity:

• Aggregation by cause of injury and by age group
• A two-CTE query comparing Traffic vs. Non-traffic hospitalisations side by side, by year
• LAG() for year-over-year comparison and growth %
• RANK() OVER (PARTITION BY ...) to rank road user types within each year
• Running totals and a 3-year moving average using window frames
• HAVING to filter on aggregated totals (age groups exceeding 40,000 hospitalisations across all years)

A few findings

• Traffic-related hospitalisations consistently outnumber non-traffic causes in this dataset by roughly 2.5x, and both are trending upward over the 11-year period.
• The 40-64 age group accounts for the largest share of traffic hospitalisations by a clear margin.
• The 3-year moving average smooths out the 2020 dip (likely COVID reduced road travel) that's visible in the raw yearly numbers. This is a good example of why smoothing matters when reading a trend.

What's next

• Reshape and load the First Nations status breakdown and state/territory breakdown datasets. This data is published as multi-pivot-table exports, not tidy data will be reshaped using SQL)
• Reconciliation queries across all three tables
• Power BI dashboard built on the same underlying data