Covid-19 transparency, please.

Forget about the wild conspiracies, I’m not going there. But I think as a society we can’t make smart decisions if we don’t have real data. Copy and paste this and email it to your local newspaper or civil registry office. It’s a sample SQL query that would show year-over-year nr. of death certificates issued by fiscal week.

Even with the inherent lag of counting deaths, I think this would provide a much clearer picture of the onset and evolution of the epidemic than most visualizations I’ve seen elsewhere. You pay taxes, you have a right to know.

-- YoY change by fiscal week.
SELECT 
    my_2019_data.fiscal_week AS fiscal_week, 
    my_2019_data.nr_death_certificates AS 2019_deaths,
    my_2020_data.nr_death_certificates AS 2020_deaths,
    CASE WHEN my_2020_data.nr_death_certificates IS NOT NULL THEN to_char( (my_2020_data.nr_death_certificates::numeric - my_2019_data.nr_death_certificates::numeric) / (my_2019_data.nr_death_certificates::numeric / 100::numeric), '999D99' ) ELSE NULL END AS yoy_change_2019_2020 
FROM
  ( SELECT fw AS fiscal_week, count(death_certificates) AS nr_death_certificates 
    FROM
      ( SELECT extract(week FROM date_deceased) AS fw, * 
        FROM my_public_records.death_certificates
        WHERE death_certificates.date_deceased >= '2019-01-01'::date
        AND death_certificates.date_deceased < '2020-01-01'::date
      ) mydata_19
    GROUP BY fw 
    ORDER BY fw 
  ) my_2019_data
LEFT JOIN
  ( SELECT fw AS fiscal_week, count(death_certificates) AS nr_death_certificates 
    FROM
      ( SELECT extract(week FROM date_deceased) AS fw, * 
        FROM my_public_records.death_certificates
        WHERE death_certificates.date_deceased >= '2020-01-01'::date
        AND death_certificates.date_deceased < '2021-01-01'::date
      ) mydata_20
    GROUP BY fw 
    ORDER BY fw 
  ) my_2020_data USING(fiscal_week);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s