Fill Covid19 stats database

fill_covid19_db.sql

--
-- tmp_covid19_data table setup
--

drop sequence if exists tmp_covid19_data_seq cascade;
create sequence tmp_covid19_data_seq;

drop table if exists tmp_covid19_data cascade;
create table tmp_covid19_data(
    id          integer not null default nextval('tmp_covid19_data_seq'),
    country     varchar(64) not null,
    confirmed   integer not null,
    deaths      integer not null,
    recovered   integer not null,
    report_date date not null,
    created_at  timestamp with time zone default CURRENT_TIMESTAMP,
    primary key(id)
);

alter sequence tmp_covid19_data_seq owned by tmp_covid19_data.id;

--
-- covid19_country table setup
--

drop sequence if exists covid19_country_seq cascade;
create sequence covid19_country_seq;

drop table if exists covid19_country cascade;
create table covid19_country(
    id          integer not null default nextval('covid19_country_seq'),
    country     varchar(64) unique not null,
    created_at  timestamp with time zone default CURRENT_TIMESTAMP,
    primary key(id)
);

alter sequence covid19_country_seq owned by covid19_country.id;

--
-- covid19_data table setup
--

drop sequence if exists covid19_data_seq cascade;
create sequence covid19_data_seq;

drop table if exists covid19_data cascade;
create table covid19_data(
    id          integer not null default nextval('covid19_data_seq'),
    country_id  integer,
    confirmed   integer not null,
    deaths      integer not null,
    recovered   integer not null,
    report_date date not null,
    created_at  timestamp with time zone default CURRENT_TIMESTAMP,
    primary key(id),
    foreign key(country_id) references covid19_country(id),
    unique (country_id, report_date)
);

alter sequence covid19_data_seq owned by covid19_data.id;

--
-- Sync covid19_data table trigger setup
--

create or replace function sync_covid19_data()
    returns trigger as
$$
begin
    insert into covid19_country(country) values(new.country)
        on conflict do nothing;

    if not exists (
        select 1
        from covid19_data d
        join covid19_country c
        on d.country_id = c.id
        where d.report_date = new.report_date
        and c.country = new.country)
    then
        insert into covid19_data(country_id, confirmed, deaths, recovered, report_date) values (
            (select id from covid19_country c where c.country = new.country),
            new.confirmed,
            new.deaths,
            new.recovered,
            new.report_date
        );
    else
        update covid19_data data set
        confirmed = new.confirmed,
        deaths = new.deaths,
        recovered = new.recovered
        where data.country_id = (
            select id from covid19_country c
            where c.country = new.country)
        and data.report_date = new.report_date;
    end if;

    delete from tmp_covid19_data where id = new.id;
    return new;
end;
$$
language 'plpgsql';

drop trigger if exists on_tmp_covid19_data_insert on tmp_covid19_data;

create trigger on_tmp_covid19_data_insert
after insert on tmp_covid19_data
for each row
execute procedure sync_covid19_data();

--
-- vcovid19 view definition

drop view if exists vcovid19;
create view vcovid19 as
select c.country
     , d.confirmed
     , d.deaths
     , d.recovered
     , d.confirmed - d.recovered - d.deaths as active
     , (case when d.confirmed = 0 then 0 else (100 * d.deaths::float/d.confirmed::float) end) as mortality_rate
     , (case when (lag(d.confirmed::float) over (order by c.country, d.report_date)) = 0 then 0 else
        100.0 * ((d.confirmed::float / (lag(d.confirmed::float) over (order by c.country, d.report_date))) - 1) end) as growth_rate
     , d.report_date
from covid19_data d
join covid19_country c
on d.country_id = c.id
order by c.country, d.report_date;