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