Counties

-- First, let's examine our data. 

select * from oregon_education.oregon_counties
order by county_ranking desc;

-- Let's examine Oregon county stats and get a sense for any relationship between the 
-- county's education rankings and its poverty level.

select 
    county,
    county_ranking,
    concat(percent_in_poverty, '%') as poverty_percentage,
    above_national_average
from oregon_education.oregon_counties
order by percent_in_poverty desc;

-- Our first look seems to indicate some sort of correlation between the two factors. 
-- What happens when we test this?

select corr(percent_in_poverty, county_ranking)
from oregon_education.oregon_counties;

-- There is some correlation between the two factors, but it isn't as strong as expected. 
-- This suggests that there are other factors influencing a county's education rankings.
-- Let's join this data with the community colleges table to start looking at the number 
-- of schools available in each county and how that might affect education ranking.

select
    counties.county as county,
    counties.county_ranking as education_ranking,
    count(colleges.school) as number_of_community_colleges
from oregon_education.oregon_counties as counties
left join oregon_education.oregon_community_colleges as colleges
on counties.county = colleges.county
group by counties.county, counties.county_ranking
order by education_ranking;

-- If we segmented the counties into three groups, what differences would we see 
-- in their demographics?

select 
    case
        when county_ranking between 1 and 12 then 'Top-ranking county'
        when county_ranking between 13 and 24 then 'Mid-ranking county'
        when county_ranking between 25 and 36 then 'Bottom-ranking county'
        else 'Missing information'
    end as county_grouping,
    round(avg(percent_highly_educated), 1) as highly_educated,
    round(avg(percent_in_poverty), 1) as in_poverty,
    round(avg(percent_white), 1) as white,
    round(avg(percent_black), 1) as black,
    round(avg(percent_american_indian), 1) as american_indian,
    round(avg(percent_asian), 1) as asian_american,
    round(avg(percent_native_hawaiian), 1) as hawaiian_pacific_islander,
    round(avg(percent_latine), 1) as latine,
    round(avg(percent_two_races), 1) as mixed_ethnicity
from oregon_education.oregon_counties
group by county_grouping;

-- The top-ranking counties have a slightly lower poverty level than the other two groupings. 
-- A first look suggests that the top-ranking counties have a larger Asian American population 
-- and a lower Native American / American Indian population than the middle- and bottom-ranking counties do.

-- What is Oregon's total population? 

select
    sum(population)
from oregon_education.oregon_counties;

-- The total population is 4,207,177.
-- According to the data for associate degrees, certificates, and OTMs issued
-- between 2007 and 2018, Oregon is predicted to issue 268,466 new degrees,
-- certificates, and OTMs between 2019 and 2030. 
-- With a goal of 300,000 issued by 2030, the gap that needs to be bridged
-- is 31,543.
-- Let's break down Oregon's total population across counties.

select
    county,
    population,
    round(((population / 4207177) * 100), 2) as percent_population
from oregon_education.oregon_counties;

-- And look at how many degrees, certificates, and OTMs need to be issued per county.

select
    *,
    round(((percent_population * 31543) / 100)) as needed_awards
from (
select
    county,
    population,
    round(((population / 4207177) * 100), 2) as percent_population
from oregon_education.oregon_counties) t
order by needed_awards desc;

-- How does the even distribution of needed awards compare to the current
-- respective county rankings? 

select
    county,
    percent_population,
    round(((percent_population * 31543) / 100)) as needed_awards,
    case
        when county_ranking between 1 and 12 then 'Top-ranking county'
        when county_ranking between 13 and 24 then 'Mid-ranking county'
        when county_ranking between 25 and 36 then 'Bottom-ranking county'
        else 'Missing information'
    end as county_grouping
from (
select
    county,
    county_ranking,
    population,
    round(((population / 4715664) * 100), 2) as percent_population
from oregon_education.oregon_counties) t
order by needed_awards desc;

-- Let's start by drilling down Marion, Linn, and Douglas counties
-- because they rank #5, #8, and #9, respectively, for additional
-- needed awards by 2030, but they're placed in the mid-ranking and 
-- bottom-ranking county groups by education levels.
-- Combined, these counties account for 3,896, or 12.35%, of the 
-- needed 31,543 additional degrees, certificates, and OTMs.