COVID19 Queries and Data Model – meshIQ ® XRay Registration

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

Query	Description
get dataset where map('type') in (1,3) and map('timeDay')='2020-06-17' order by map('totalConfirmed') desc	Gets all data of countries and other objects for specified ‘timeDay’ (which is usually last available day computed using queries above). In this example the date is set to 2020-06-17 This query brings data for: • countries table • “Most” widgets • “Density Per 1M” countries table
get first 2 dataset fields sum(map('totalConfirmed')),sum(map('totalDeaths')),sum(map('totalRecovered')),sum(map('totalActive')),sum(map('confirmed')),sum(map('deaths')),sum(map('recovered')),sum(map('active')),max(map('refreshTime')) where map('type')in (1,3) for last 20 days group by map('timeDay') order by UpdateTime desc	Get all countries and objects aggregated data for last day and day before last day. This query collects attributes for “Total” widgets”.
get dataset fields sum(map('totalConfirmed')), sum (map('totalDeaths')), sum(map('totalRecovered')),sum(map('totalActive')),sum (map('confirmed')),sum (map('deaths')),sum (map('recovered')),sum (map('active')) where map('type') in( 1,3) for last 10 years group by UpdateTime bucketed by day order by UpdateTime asc	Query collects all planet history (includes countries and objects daily records). This query brings data for “CFR History”, “RFR History” and “24h Changes History” charts. CFR, RFR are computed in service using main attributes which stores accumulated values.
get first 10 dataset fields sum(map('confirmed')), sum(map('deaths')), sum(map('recovered')), sum(map('active')) where map('type')=1 for last 7 days group by map('countryRegion') order by Sum(Properties('confirmed')) desc	Query collects top 10 countries by sum of confirmed cases during last 7 days. Brings data to “Top 10” widget “Confirmed” tab.
get first 10 dataset fields sum(map('confirmed')), sum(map('deaths')), sum(map('recovered')), sum(map('active')) where map('type')=1 for last 7 days group by map('countryRegion') order by Sum(Properties('deaths')) desc	Query collects top 10 countries by sum of deaths cases during last 7 days. Brings data to “Top 10” widget “Deaths” tab.
get first 10 dataset fields sum(map('confirmed')), sum(map('deaths')), sum(map('recovered')), sum(map('active')) where map('type')=1 for last 7 days group by map('countryRegion') order by Sum(Properties('recovered')) desc	Query collects top 10 countries by sum of recovered cases during last 7 days. Brings data to “Top 10” widget “Recovered” tab.
get first 10 dataset fields sum(map('confirmed')), sum(map('deaths')), sum(map('recovered')), sum(map('active')) where map('type')=1 for last 7 days group by map('countryRegion') order by Sum(Properties('active')) desc	Query collects top 10 countries by sum of active cases during last 7 days. Brings data to “Top 10” widget “Active” tab.
get first 10 dataset fields sum(map('confirmedPopulation1M')), sum(map('deathsPopulation1M')), sum(map('recoveredPopulation1M')), sum(map('activePopulation1M')) where map('type')=1 for last 7 days group by map('countryRegion') order by Sum(Properties('confirmedPopulation1M')) desc	Query collects top 10 countries by sum of confirmed cases per 1M population during last 7 days. Brings data to “Density” Tab “Top 10” widget “Confirmed” tab.
get first 10 dataset fields sum(map('confirmedPopulation1M')), sum(map('deathsPopulation1M')), sum(map('recoveredPopulation1M')), sum(map('activePopulation1M')) where map('type')=1 for last 7 days group by map('countryRegion') order by Sum(Properties('deathsPopulation1M')) desc	Query collects top 10 countries by sum of deaths cases per 1M population during last 7 days. Brings data to “Density” Tab “Top 10” widget “Deaths” tab.
get first 10 dataset fields sum(map('confirmedPopulation1M')), sum(map('deathsPopulation1M')), sum(map('recoveredPopulation1M')), sum(map('activePopulation1M')) where map('type')=1 for last 7 days group by map('countryRegion') order by Sum(Properties('recoveredPopulation1M')) desc	Query collects top 10 countries by sum of recovered cases per 1M population during last 7 days. Brings data to “Density” Tab “Top 10” widget “Recovered” tab.
get first 10 dataset fields sum(map('confirmedPopulation1M')), sum(map('deathsPopulation1M')), sum(map('recoveredPopulation1M')), sum(map('activePopulation1M')) where map('type')=1 for last 7 days group by map('countryRegion') order by Sum(Properties('activePopulation1M')) desc	Query collects top 10 countries by sum of active cases per 1M population during last 7 days. Brings data to “Density” Tab “Top 10” widget “Active” tab.

Country details page queries

Query	Description
get first 1 dataset where map('countryRegion')='US' and map('type') in (1) for latest 10 years order by UpdateTime desc	Gets last record for a specified country. Used for ‘Total’ widgets.In this example the country is set to US.
get first 2 dataset where map('countryRegion')='US' and map('type') in (1) for latest 10 years order by UpdateTime desc	Gets last 2 records for a specified country. Used for ‘Total’ widgets. Used for USA cases, for computation changes of hospitalized, ICU,Ventilator. In this example the country is set to US.
get dataset where map('type')=2 and map('timeDay')='2020-06-17' and map('countryRegion')='US' order by map('totalConfirmed') desc	Collects states daily records for specified country and specified ‘timeDay’. Brings data to ‘Country states/provinces’ table and ‘Hospitalization & Tests’ table. In this example the date is set to 2020-06-17 and the country is set to US.
get dataset where map('type')=1 and map('countryRegion')='?' for last 10 years order by UpdateTime asc	Brings data to “CFR History”, “RFR History”, “R0(SI)”, “24h Changes History”, “Hospitalization & Tests” charts.In this example the country is set to US.

Country state details page queries

Query	Description
get first 1 dataset where map('type')=2 and map('countryRegion')='US' and map('provinceState')='New York' for latest 10 years order by UpdateTime desc	Collects last record for a specified country state. In this example the country is set to US and the province is set to New York.
	Brings data to State details “Total” widgets.
get dataset where map('type') in (4) and map('countryRegion')='US' and map ('provinceState')='New York' and map('timeDay')='2020-06-17' order by map('totalConfirmed') desc	Collects cities records for a specified ‘timeDay’ and specified country state. Brings data to cities table in country/state details page. In this example the country is set to US, the State is set to New York and the date is set to 2020-06-17.
get dataset where map('type')=2 and map('countryRegion')='US' and map('provinceState')='New York' for last 10 years order by UpdateTime asc	In this example the country is set to US and the province is set to New York.
	Brings data to “CFR History”, “24h Changes History” charts.

Country state city details page queries

Query	Description
get first 1 dataset where map('type')=2 and map('countryRegion')='US' and map('provinceState')='New York' for latest 10 years order by UpdateTime desc	Collects last record for a specified country state. In this example the country is set to US and the state is set to New York.
	Brings data to State details “Total” widgets.
get dataset where map('type') in (4) and map('countryRegion')='US' and map ('provinceState')='New York' and map('timeDay')='2020-06-17' order by map('totalConfirmed') desc	Collects cities records for a specified ‘timeDay’ and specified country state. Brings data to cities table in country/state details page. In this example the country is set to US and the state is set to New York and the date is set to 2020-06-17
get dataset where map('type')=2 and map('countryRegion')='US' and map('provinceState')='New York' for last 10 years order by UpdateTime asc	In this example the country is set to US and the state is set to New York.
	Brings data to “CFR History”, “24h Changes History” charts.

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

COVID19 Repository data model

Field	Data Type	Description
DatasetID	String	Represents unique identifier of any type of record, it consists of:
		· date (yyyy-MM-dd)
		· country name
		· state name
		· city name
		· object name
		According to record type appropriate part is added to its identifier.
DatasetName	String	The name of the data set to which daily records belongs. Currently all country records go under the same data set called “covid19_daily_record”.
UpdateTime	Date	The time stamp when record was created updated.
refreshTime	Date	The timestamp when the record was refreshed.
sourceTimestamp	Date	The timestamp of the record which is from its raw source (never changes).
timeDay	String	String representation of the day when the record was created/updated.
dataSource	String	The name of the data source from which record data is originated.
type	String	The type of the record:
		· 1 – country
		· 2 – state
		· 3 – any object
		· 4 – city
		· 5 – global
continent	String	The name of the continent.
countryRegion	String	The name of the country of region.
countryArea	Decimal	The area of the country in square kilometers.
code	Integer	UID of the country. See details on the link below this table
fips	Integer	US only. Federal Information Processing Standards code that uniquely identifies counties within the USA.
iso2	String	Code of the country.
iso3	String	Code of the country.
population	Integer	Population of the country, state, city
countryPopulationDensity	Decimal	Population density per square kilometer of the country.
latitude	Decimal	Latitude of the country, state, city.
longitude	Decimal	Longitude of the country, state, city.
provinceState	String	The name of the province/state.
city	String	The name of the city.
totalConfirmed	Integer	Total confirmed cases (cumulative).
totalConfirmedDensity	Decimal	Total confirmed cases per area (square kilometers).
totalConfirmedPerPopulationDensity	Decimal	Total confirmed cases per population density in the area.
totalConfirmedPopulation1M	Decimal	Total confirmed cases per 1 million population.
totalConfirmedTotalPopulationPct	Decimal	Total confirmed cases percentage per area population.
totalDeaths	Integer	Total death cases (cumulative).
totalDeathsDensity	Decimal	Total death cases per area (square kilometers).
totalDeathsPerPopulationDensity	Decimal	Total death cases per population density in the area.
totalDeathsPopulation1M	Decimal	Total death cases per 1 million population.
totalDeathsTotalPopulationPct	Decimal	Total death cases percentage per area population.
totalRecovered	Integer	Total recovered cases (cumulative).
totalRecoveredDensity	Decimal	Total recovered cases per area (square kilometers).
totalRecoveredPerPopulationDensity	Decimal	Total recovered cases per population density in the area.
totalRecoveredPopulation1M	Decimal	Total recovered cases per 1 million population.
totalRecoveredTotalPopulationPct	Decimal	Total recovered cases percentage per area population.
totalActive	Integer	Total active cases (cumulative).
totalActiveDensity	Decimal	Total active cases per area (square kilometers).
totalActivePerPopulationDensity	Decimal	Total active cases per population density in the area.
totalActivePopulation1M	Decimal	Total active cases per 1 million population.
totalActiveTotalPopulationPct	Decimal	Total active cases percentage per area population.
confirmed	Integer	Confirmed cases change since last day.
confirmedPct	Decimal	Confirmed cases change percentage since last day.
confirmedDensity	Decimal	Confirmed cases change per area (square kilometers) since last day.
confirmedPerPopulationDensity	Decimal	Confirmed cases change per population density since last day.
confirmedPopulation1M	Decimal	Confirmed cases change per 1 million population since last day.
confirmedTotalPopulationPct	Decimal	Confirmed cases change per area population since last day.
deaths	Integer	Death cases change since last day.
deathsPct	Decimal	Death cases change percentage since last day.
deathsDensity	Decimal	Death cases change per area (square kilometers) since last day.
deathsPerPopulationDensity	Decimal	Death cases change per population density since last day.
deathsPopulation1M	Decimal	Death cases change per 1 million population since last day.
deathsTotalPopulationPct	Decimal	Death cases change per area population since last day.
recovered	Integer	Recovered cases change since last day.
recoveredPct	Decimal	Recovered cases change percentage since last day.
recoveredDensity	Decimal	Recovered cases change per area (square kilometers) since last day.
recoveredPerPopulationDensity	Decimal	Recovered cases change per population density since last day.
recoveredPopulation1M	Decimal	Recovered cases change per 1 million population since last day.
recoveredTotalPopulationPct	Decimal	Recovered cases change per area population since last day.
active	Integer	Active cases change since last day.
activePct	Decimal	Active cases change percentage since last day.
activeDensity	Decimal	Active cases change per area (square kilometers) since last day.
activePerPopulationDensity	Decimal	Active cases change per population density since last day.
activePopulation1M	Decimal	Active cases change per 1 million population since last day.
activeTotalPopulationPct	Decimal	Active cases change per area population since last day.
cfrPct	Decimal	Case fatality rate percentage.
rfrPct	Decimal	Resolved fatality rate percentage.
r0SI	Decimal	Base reproduction number based on SI model.
herdImmunityR0SI	Decimal	Herd immunity indicator based on R0SI number.
totalHospitalizedAmount	Integer	Total hospitalized cases (cumulative).
totalHospitalizedInICU	Integer	Total hospitalized in ICU cases (cumulative).
totalHospitalizedOnVentilator	Integer	Total hospitalized on Ventilator cases (cumulative).
totalTestsResults	Integer	Total tests made (cumulative).
totalTestsResultsNegative	Integer	Total negative tests (cumulative).
totalTestsResultsPositive	Integer	Total positive tests (cumulative).
hospitalizedAmount	Integer	Hospitalized change since last day.
hospitalizedAmountActive	Integer	Active hospitalized now.
hospitalizedInICUActive	Integer	Active in ICU now.
hospitalizedOnVentilatorActive	Integer	Active on Ventilator now.
testsResults	Integer	Tests results change since last day.
testsResultsNegative	Integer	Tests results negative change since last day.
testsResultsPositive	Integer	Tests results positive change since last day.
testsResultsPending	Integer	Tests results pending.

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