EDA.sql

use road_trauma;

# Total hospitalisations as per the cause of injury
select cause_of_injury, sum(hospitalisations) as TOTAL_HOSPITALISATIONS from national_monthly_facts 
group by cause_of_injury ORDER BY TOTAL_HOSPITALISATIONS DESC;

# Split by age group
select age_group, sum(hospitalisations) as TOTAL_HOSPITALISATIONS from national_monthly_facts 
where cause_of_injury = 'Traffic'
group by age_group
ORDER BY TOTAL_HOSPITALISATIONS DESC;

#Comparing Traffic and Non-traffic cases based on the calendyar year
With traffic as(
Select calendar_year, Sum(hospitalisations) as Traffic_hospitalisations 
from national_monthly_facts 
where cause_of_injury = "Traffic"
group by calendar_year),
non_traffic as (
Select calendar_year, Sum(hospitalisations) as Non_Traffic_hospitalisations 
from national_monthly_facts 
where cause_of_injury = "Non-Traffic"
group by calendar_year)
Select traffic.calendar_year, Non_Traffic_hospitalisations, Traffic_hospitalisations 
from traffic 
join non_traffic on traffic.calendar_year = non_traffic.calendar_year
order by traffic.calendar_year;

# Comparison with the previous year values
WITH traffic AS (
    SELECT calendar_year, SUM(hospitalisations) AS total_hosp
    FROM national_monthly_facts
    WHERE cause_of_injury = 'Traffic'
    GROUP BY calendar_year
)
SELECT 
    calendar_year,
    total_hosp,
    LAG(total_hosp) OVER (ORDER BY calendar_year) AS prev_year_hosp
FROM traffic
ORDER BY calendar_year;

# Calculating year to year growth percentage in traffic hospitalisations
With traffic as(
Select calendar_year, Sum(hospitalisations) as Traffic_hospitalisations 
from national_monthly_facts 
where cause_of_injury = "Traffic"
group by calendar_year),
with_lag AS(
SELECT calendar_year, Traffic_hospitalisations, LAG(Traffic_hospitalisations) OVER (ORDER BY calendar_year) as prev_year_hospitalisations
from traffic)
SELECT with_lag.calendar_year, Traffic_hospitalisations, prev_year_hospitalisations,  ((Traffic_hospitalisations - prev_year_hospitalisations)/prev_year_hospitalisations) * 100 as per_year_growth
from with_lag
order by calendar_year;

#Ranking the traffic hospitalisations as per the road user type
With yearly_road_user AS(
Select calendar_year, road_user, Sum(hospitalisations) as Traffic_hospitalisations 
from national_monthly_facts 
where cause_of_injury = "Traffic"
group by calendar_year, road_user)
Select calendar_year, road_user, Traffic_hospitalisations,
RANK() OVER (PARTITION BY calendar_year ORDER BY Traffic_hospitalisations DESC) as ranking
FROM yearly_road_user;

# Running total 
WITH traffic AS (
    SELECT calendar_year, SUM(hospitalisations) AS Traffic_hospitalisations 
    FROM national_monthly_facts
    WHERE cause_of_injury = 'Traffic'
    GROUP BY calendar_year
)
SELECT 
    calendar_year,
  Traffic_hospitalisations ,
    SUM(Traffic_hospitalisations ) OVER (ORDER BY calendar_year) AS running_total
FROM traffic
ORDER BY calendar_year;

# Sliding Window average of three years
With traffic as (
Select calendar_year, Sum(hospitalisations) as Traffic_hospitalisations 
from national_monthly_facts
Where cause_of_injury = "Traffic"
Group BY calendar_year)
Select calendar_year, Traffic_hospitalisations,
AVG(Traffic_hospitalisations) OVER (ORDER BY calendar_year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3_years
From traffic
ORDER BY calendar_year;

# HAVING Clause 
Select age_group, Sum(hospitalisations) as Traffic_hospitalisations
from national_monthly_facts
Where cause_of_injury = "Traffic"
GROUP BY age_group
HAVING Traffic_hospitalisations > 40000;