Published
Edited
Apr 29, 2022
1 fork
all_cases = await splitgraph`
SELECT
date,
confirmed,
recovered,
dead,
source
FROM "splitgraph/oxcovid19".epidemiology
WHERE countrycode = 'GBR' AND gid = '{"GBR"}' AND source = 'GBR_PHE'
ORDER BY date DESC`
geo = await splitgraph`
WITH
cases_by_l3_gid AS (
SELECT
date,
UNNEST(gid) AS gid,
SUM(tested) AS tested,
SUM(confirmed) AS confirmed,
SUM(dead) AS dead
FROM "splitgraph/oxcovid19".epidemiology e
WHERE countrycode = 'GBR'
AND SOURCE LIKE 'GBR_%'
AND adm_area_3 IS NOT NULL
GROUP BY UNNEST(gid), date),
latest_cases_by_gid AS (
SELECT
date,
gid,
tested,
confirmed,
dead
FROM (
SELECT
*,
RANK() OVER (PARTITION BY gid ORDER BY date DESC)
FROM cases_by_l3_gid) r
WHERE rank = 1)
SELECT
c.date,
c.gid,
ad.adm_area_3 AS adm_area_3,
c.tested,
c.confirmed,
c.dead,
ad.geometry
FROM latest_cases_by_gid c
JOIN "splitgraph/oxcovid19".administrative_division ad
ON c.gid = ad.adm_area_3_code
WHERE ad.countrycode = 'GBR'
ORDER BY gid;`;
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
