COVID-19: API to PostgreSQL Pipeline

create or replace view covid.dim_country as (
    select iso3, country, continent, population
    from covid.database
);

create or replace view covid.fact_cases as (
    select iso3, cases, deaths, recovered, active, critical, tests
    from covid.database
);

# 1. pull live country data from the open disease.sh API
url = "https://disease.sh/v3/covid-19/countries"
data = requests.get(url).json()
df = pd.DataFrame(data)

# 2. flatten the nested countryInfo column
df["iso3"] = df["countryInfo"].str.get("iso3")
df["lat"]  = df["countryInfo"].str.get("lat")
df["long"] = df["countryInfo"].str.get("long")
df.drop("countryInfo", axis=1, inplace=True)

# 3. drop the two cruise ships (no iso3, no population, no continent)
df.dropna(inplace=True)
df.reset_index(drop=True, inplace=True)

# 4. epoch milliseconds -> real date
df["updated"] = pd.to_datetime(df["updated"], unit="ms").dt.normalize()

# 5. drop columns that are empty or almost empty
df.drop(["today_cases", "today_deaths", "today_recovered"], axis=1, inplace=True)

# 6. load into PostgreSQL (credentials stay outside the notebook)
df.to_sql("database", engine, schema="covid", if_exists="replace", index=False)

-- highest fatality rate
select country, continent,
       round(100 * (deaths::numeric / cases), 2) as fatality_rate
from covid.fact_cases fc
join covid.dim_country dc on fc.iso3 = dc.iso3
order by fatality_rate desc
limit 5;

-- each continent's share of world cases (window function)
select continent,
       sum(cases) as total_cases,
       round(100 * sum(cases) / sum(sum(cases)) over (), 2) as share_pct
from covid.fact_cases fc
join covid.dim_country dc on fc.iso3 = dc.iso3
group by continent
order by total_cases desc;

create view covid.metric_per_continent as
with fatality as (
    select continent,
           sum(cases)      as total_cases,
           sum(population) as total_population,
           sum(critical)   as total_critical_cases,
           sum(deaths)     as total_deaths,
           sum(recovered)  as total_recovered,
           round(100 * sum(deaths)::numeric / sum(cases), 2) as fatality_rate
    from covid.fact_cases fc
    join covid.dim_country dc on fc.iso3 = dc.iso3
    group by continent
),
avg_fatality as (
    select round(avg(fatality_rate), 2) as avg_fatality_rate from fatality
)
select continent, total_cases, total_population, total_critical_cases,
       total_deaths, total_recovered, fatality_rate,
       case when fatality_rate > avg_fatality_rate then 'High'
            when fatality_rate = avg_fatality_rate then 'Average'
            else 'Low' end as fatality_category
from fatality
cross join avg_fatality;