COVID19 Queries and Data Model

COVID19 XRay Queries

Queries for Obtaining Last Records In The Data Lake

Query Description
get first 1 dataset fields map('timeDay') where map('type')=1 order by UpdateTime desc Gets ‘timeDay’ of last country daily record
get first 1 dataset fields map('timeDay') where map('type')=2 and map('countryRegion')='?' order by UpdateTime desc Gets ‘timeDay’ of last state record for specified country. Replace ? With the desired country or region - for example US
get first 1 dataset fields map('timeDay') where map('type')=4 and map('countryRegion')='US' and map('provinceState')='New York' order by UpdateTime desc Gets ‘timeDay’ of last city record for specified country and state. Change the ? With the desired choices, for example countryRegion US and provinceState New York

(used to compute a time frame of last available data)

Overview page queries

Country details page queries

Country state details page queries

Country state city details page queries

All records in Covid19 repository are collected as daily records for each country, state city.

COVID19 Repository data model

UID of the country.

See details: https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/UID_ISO_FIPS_LookUp_Table.csv

COVID-19 Dashboard Queries in XRay

Summary

Total Confirmed

get first 1 dataset fields sum(map(‘totalConfirmed’)) as ‘Total Confirmed’ where map(‘type’) in (1,3) for last 20 days group by map(‘timeDay’) order by UpdateTime desc show as summary


Total Deaths


get first 1 dataset fields sum(map(‘totalDeaths’)) as ‘Total Deaths’ where map(‘type’) in (1,3) for last 20 days group by map(‘timeDay’) order by UpdateTime desc show as summary


Total Recovered


get first 1 dataset fields sum(map(‘totalRecovered’)) as ‘Total Recovered’ where map(‘type’) in (1,3) for last 20 days group by map(‘timeDay’) order by UpdateTime desc show as summary


Total Active


get first 1 dataset fields sum(map(‘totalActive’)) as ‘Total Active’ where map(‘type’) in (1,3) for last 20 days group by map(‘timeDay’) order by UpdateTime desc show as summary

Most Confirmed

get first 1 dataset fields max(map(‘totalConfirmed’)) as ‘Total Confirmed’ where map(‘type’) in (1) for last 20 days group by map(‘countryRegion’) order by Max(Properties(‘totalConfirmed’)) desc show as summary


Most Deaths


get first 1 dataset fields max(map(‘totalDeaths’)) as ‘Total Deaths’ where map(‘type’) in (1) for last 20 days group by map(‘countryRegion’) order by Max(Properties(‘totalDeaths’)) desc show as summary


Most Recovered


get first 1 dataset fields max(map(‘totalRecovered’)) as ‘Total Recovered’ where map(‘type’) in (1) for last 20 days group by map(‘countryRegion’) order by Max(Properties(‘totalRecovered’)) desc show as summary


Most Active


get first 1 dataset fields max(map(‘totalActive’)) as ‘Total Active’ where map(‘type’)in (1) for last 20 days group by map(‘countryRegion’) order by Max(Properties(‘totalActive’)) desc show as summary

VIEWLETS

24h changes history – Confirmed

get dataset fields sum (map(‘confirmed’)) as ‘Confirmed’ where map(‘type’) in (1,3) for last 10 years group by UpdateTime bucketed by day order by UpdateTime desc show as linechart


24h changes history – Deaths


get dataset fields sum (map(‘deaths’)) as ‘Deaths’ where map(‘type’) in (1,3) for last 10 years group by UpdateTime bucketed by day order by UpdateTime asc show as linechart


CFR History for USA


get dataset fields UpdateTime, map(‘cfrPct’) as ‘CFR’ where map(‘type’) in (1) and map(‘countryRegion’)=’US’ for last 10 years order by UpdateTime asc show as linechart


RFR History for USA


get dataset fields UpdateTime, map(‘rfrPct’) as ‘RFR’ where map(‘type’) in (1) and map(‘countryRegion’)=’US’ for last 10 years order by UpdateTime asc show as linechart



COVID-19 Overview


get dataset fields countryRegion as ‘Country’, totalConfirmed as ‘Total Confirmed’,confirmed as ‘Confirmed 24h Chng’, confirmedPct as ‘Confirmed 24h Chng%’, totalDeaths as ‘Total Deaths’,deaths as ‘Deaths 24h Chng’,deathsPct as ‘Deaths 24h Chng%’, cfrPct as ‘CFR’,rfrPct as ‘RFR’,r0SI as ‘R0 (SI)’, totalRecovered as ‘Total Recovered’, recovered as ‘Recovered 24h Chng’, recoveredPct as ‘Recovered 24h Chng%’, totalActive as ‘Total Active’, map(‘active’) as ‘Active 24h Chng’, activePct as ‘Active 24h Chng%’ where map(‘type’)=1 for latest 1 day order by totalConfirmed desc


Top 10 Countries By Confirmed Cases (for last 7 days)


get first 20 dataset fields sum(map(‘confirmed’)) as ‘Confirmed’, sum(map(‘deaths’)) as ‘Deaths’, sum(map(‘recovered’)) as ‘Recovered’, sum(map(‘active’)) as ‘Active’ where map(‘type’)=1 for last 7 days group by map(‘countryRegion’) order by Sum(Properties(‘confirmed’)) desc