The Snowflake Data Marketplace is quickly becoming one of my favourite things about working at Snowflake. With the marketplace I have live, instant access to some of the worlds best datasets to give my data deeper context and meaning. I had the pleasure of recently presenting a virtual hands-on lab (recording) with Snowflake using Starschema’s COVID-19 dataset (link) and Wunderman Thompson’s COVID-19 Risk, Readiness and Recovery dataset (link) built on the backbone of their fabulous Amerilink Insights dataset. I highly recommend both datasets to drive a deeper understanding of our collective pandemic context. The best part is they are both free to play with, free! I love me some free data…let’s geek out!

Getting and querying data from the Snowflake Data Marketplace is dead simple and built on a very powerful premise. After 3 clicks of the mouse, I have data that is ready for analysis and always in sync with Starschema’s or Wundermand’s latest updates. This means I have instant access to a single-source of truth on COVID-19 cases as reported by public health authorities around the world along with key economic drivers to enhance my data.
Getting Started
First, you will need a Snowflake account if you want to run these queries. Snowflake offers free trials (link) to get started, even with these powerful datasets. If you have an existing account you must be the ACCOUNTADMIN to follow along. Grab a full copy of the SQL file (here).
Login to your Snowflake account and switch your context to ACCOUNTADMIN.

Browse to the Data Marketplace.

Get the Starschema COVID-19 set by browsing to the Health category or searching.

Get the data and assign to roles that need access, keep the name as “STARSCHEMA_COVID19” or the later queries will not work.

Starschema COVID-19 Data Set Queries
Now we are ready to start querying the data, please go ahead and create a new worksheet and paste this code for your own use. Be sure to set the session variables to a meaningful context or these queries will not run. Grab a full copy of the SQL file (here).
--+-----------------------------------------------------------------------------------------------+-- --+-----------------------------------------------------------------------------------------------+-- -- <Webinar SQL> -- <Getting Started with the Snowflake Data Marketplace> -- <September 9, 2020 | 10:00am PST> -- <SQL File | David A Spezia> -- <Sales Engineer | Snowflake> --+-----------------------------------------------------------------------------------------------+-- --+-----------------------------------------------------------------------------------------------+-- --Session Variables --Date to Use SELECT DISTINCT DATE FROM PUBLIC.JHU_COVID_19 ORDER BY 1 DESC; --Set Manually or From the MAx Value in the Data SET DATE_VARIALBE = '2020-08-31'; SET DATE_VARIABLE = (SELECT TOP 1 DATE FROM PUBLIC.JHU_COVID_19 ORDER BY 1 DESC); SELECT $DATE_VARIABLE; --Date Floor for some Charts SET DATE_FLOOR_VARIABLE = '2020-06-01'; SELECT $DATE_FLOOR_VARIABLE; --Difference ><20 Makes Sense For My Charts SELECT DATEDIFF('day',TO_DATE($DATE_FLOOR_VARIABLE),TO_DATE($DATE_VARIABLE)); --Country to Use, instead of Hard Coding USA SELECT DISTINCT COUNTRY_REGION FROM PUBLIC.JHU_COVID_19 ORDER BY 1 DESC; SET COUNTRY_VARIABLE = 'United States'; SELECT $COUNTRY_VARIABLE; --+-----------------------------------------------------------------------------------------------+-- -- Star Schema JHU Data --+-----------------------------------------------------------------------------------------------+-- --GET() Star Schema COVID-19 as "STARSCHEMA_COVID19" AS ACCOUNTADMIN IN Discover UI USE ROLE ACCOUNTADMIN; USE DATABASE STARSCHEMA_COVID19; USE SCHEMA PUBLIC; --Describe Table DESC TABLE JHU_COVID_19; --JHU_COVID_19 Columns SELECT COLUMN_NAME, DATA_TYPE, COMMENT FROM STARSCHEMA_COVID19.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'JHU_COVID_19'; --Limit 10 from JHU_COVID_19 SELECT * FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 LIMIT 10; --Cases vs Difference SELECT SUM(CASES), CASE_TYPE, COUNTRY_REGION FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 WHERE DATE<=TO_DATE($DATE_VARIABLE) AND COUNTRY_REGION=$COUNTRY_VARIABLE GROUP BY COUNTRY_REGION, CASE_TYPE; --Cases vs Difference 2.0 SELECT SUM(CASES), SUM(DIFFERENCE), CASE_TYPE, COUNTRY_REGION FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 WHERE DATE<=TO_DATE($DATE_VARIABLE) AND COUNTRY_REGION=$COUNTRY_VARIABLE GROUP BY COUNTRY_REGION, CASE_TYPE; --Bar Chart: SUM([DIFFERNCE]) by [CASE TYPE] on X-Axis SELECT SUM(DIFFERENCE), CASE_TYPE, COUNTRY_REGION FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 WHERE DATE<=TO_DATE($DATE_VARIABLE) AND COUNTRY_REGION=$COUNTRY_VARIABLE GROUP BY COUNTRY_REGION, CASE_TYPE; --Line Chart: Add [DATE] Column to Visual ON X-Axis --Add [CASE_TYPE] to Series SELECT COUNTRY_REGION, CASE_TYPE, DATE, RUNNING_SUM_COUNTRY FROM ( SELECT COUNTRY_REGION, CASE_TYPE, DATE, SUM(DIFFERENCE) OVER(PARTITION BY COUNTRY_REGION, CASE_TYPE ORDER BY DATE) AS RUNNING_SUM_COUNTRY FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 WHERE COUNTRY_REGION=$COUNTRY_VARIABLE) WHERE DATE>=TO_DATE($DATE_FLOOR_VARIABLE) GROUP BY COUNTRY_REGION, CASE_TYPE, DATE, RUNNING_SUM_COUNTRY ORDER BY COUNTRY_REGION, CASE_TYPE, DATE; --Line Chart: by Country > 500000 --Add [COUNTRY_REGION] to Series SELECT COUNTRY_REGION, CASE_TYPE, DATE, RUNNING_SUM_COUNTRY FROM ( SELECT COUNTRY_REGION, CASE_TYPE, DATE, SUM(DIFFERENCE) OVER(PARTITION BY COUNTRY_REGION, CASE_TYPE ORDER BY DATE) AS RUNNING_SUM_COUNTRY FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU WHERE (SELECT SUM(JHU_COVID_19.DIFFERENCE) FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU_COVID_19 WHERE JHU_COVID_19.COUNTRY_REGION = JHU.COUNTRY_REGION AND JHU_COVID_19.CASE_TYPE = 'Confirmed') > 500000 AND CASE_TYPE = 'Confirmed') WHERE DATE>=TO_DATE($DATE_FLOOR_VARIABLE) GROUP BY COUNTRY_REGION, CASE_TYPE, DATE, RUNNING_SUM_COUNTRY ORDER BY COUNTRY_REGION, CASE_TYPE, DATE;

Safegraph US Census Data & Neighborhood Insights Data Set
Now you are ready to augment the JHU data with populations. Thankfully, Safegraph has a free version of their US Census Data & Neighborhood Insights dataset. Get the Safegraph data from the Demographics category, get the database and assign to roles that need access, change the name to “SAFEGRAPH_SHARE” or the later queries will not work.

Safegraph Queries
--+-----------------------------------------------------------------------------------------------+-- -- Safe Graph Data --+-----------------------------------------------------------------------------------------------+-- --GET() Safe Graph Data As "SAFEGRAPH_SHARE" SELECT FIPS, PROVINCE_STATE, COUNTY FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 WHERE STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19.COUNTRY_REGION = $COUNTRY_VARIABLE AND STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19.DATE = TO_DATE($DATE_VARIABLE); --Result Set: 100k Population SQL Result Set SELECT PROVINCE_STATE, SUM(CONFIRMED_CASES)/SUM(COUNTY_POPULATION)*100000 AS CASES_PER_100K, SUM(COUNTY_POPULATION) AS STATE_POPULATION , SUM(CONFIRMED_CASES) AS CONFIRMED_CASES FROM (SELECT JHU.FIPS, SUM(JHU.CASES) AS CONFIRMED_CASES, JHU.PROVINCE_STATE, JHU.COUNTY, JHU.DATE, GEOSQL.COUNTY_POPULATION, IFF(SUM(JHU.DIFFERENCE)>0,GEOSQL.COUNTY_POPULATION,0) AS AT_RISK FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU LEFT JOIN (SELECT LEFT(CBG,5) AS FIPS, SUM(GEO.TOTAL_POPULATION) AS COUNTY_POPULATION FROM SAFEGRAPH_SHARE.PUBLIC.US_POPULATION_BY_SEX_GEO GEO GROUP BY FIPS) GEOSQL ON GEOSQL.FIPS = JHU.FIPS WHERE JHU.COUNTRY_REGION = $COUNTRY_VARIABLE AND JHU.DATE = TO_DATE($DATE_VARIABLE) AND JHU.CASE_TYPE='Confirmed' GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, JHU.FIPS, GEOSQL.COUNTY_POPULATION, JHU.DATE ORDER BY JHU.FIPS) GROUP BY PROVINCE_STATE HAVING CASES_PER_100K IS NOT NULL ORDER BY CASES_PER_100K DESC; --HAVING PROVINCE_STATE = 'California';

Weathersource Weather & Climate Data for BI Data Set
Now you are ready to further augment the JHU data with weather. Weathersource has a free version of their US Weather & Climate for BI dataset. Be sure to get that specific dataset. Get the Weathersource data from the Weather category, get the database and assign to roles that need access, change the name to “WEATHERSOURCE_PARTNER_WS_ONPOINT_WEATHER_DATA_SHARE” or the later queries will not work.

Weathsource Queries
--+-----------------------------------------------------------------------------------------------+-- -- Weather Source Data --+-----------------------------------------------------------------------------------------------+-- --GET() Weather Source Data As "WEATHERSOURCE_PARTNER_WS_ONPOINT_WEATHER_DATA_SHARE" SELECT COUNT(*) FROM WEATHERSOURCE_PARTNER_WS_ONPOINT_WEATHER_DATA_SHARE.PUBLIC.HISTORY_DAY_COVID_19; --Simple Test Query SELECT DATE_VALID_STD, AVG(AVG_TEMPERATURE_AIR_2M_F) FROM WEATHERSOURCE_PARTNER_WS_ONPOINT_WEATHER_DATA_SHARE.PUBLIC.HISTORY_DAY_COVID_19 WHERE COUNTRY = 'US' AND POSTAL_CODE LIKE '100%' GROUP BY DATE_VALID_STD ORDER BY DATE_VALID_STD DESC; --Joining COVID Data with Weather Data SELECT W.DATE,W.AVG_TEMP, C.COUNTY, C.CASES, C.CASES_SINCE_PREV_DAY FROM ( SELECT DATE_VALID_STD DATE, AVG(AVG_TEMPERATURE_AIR_2M_F) AVG_TEMP FROM WEATHERSOURCE_PARTNER_WS_ONPOINT_WEATHER_DATA_SHARE.PUBLIC.HISTORY_DAY_COVID_19 WHERE COUNTRY = 'US' AND POSTAL_CODE LIKE '100%' GROUP BY DATE_VALID_STD ORDER BY DATE_VALID_STD ) W INNER JOIN ( SELECT DATE, COUNTY, CASES, CASES_SINCE_PREV_DAY FROM STARSCHEMA_COVID19.PUBLIC.NYT_US_COVID19 WHERE COUNTY = 'New York City' ORDER BY DATE ) C ON W.DATE=C.DATE;

Wunderman COVID-19 Risk, Readiness & Recovery
Now you are ready to fully augment the JHU data with the amazing Wunderman data. Get the Wunderman Amerilink Insights data from the Marketing section, get the database and assign to roles that need access, change the name to “WUNDERMAN” or the later queries will not work.
The free, ready to query data set on the marketplace is just a random 1% sample of the overall data from the Wunderman data set. Request the “Personalized” version of the data set from the Marketplace to enter into a conversation with Wunderman Thompson about acquiring the unmasked, full data set for your needs.

Wunderman Queries
--+-----------------------------------------------------------------------------------------------+-- -- Wunderman Risk & Recovery Data --+-----------------------------------------------------------------------------------------------+-- /* --GET() Wunderman Thompson AmeriLINK Insights Data Set as "WUNDERMAN" ---> Queries we will write <-- + Age by State + 100k Infections by Avg(Age) by State + Respitory Issues per 100k by State + 100k Infections by Respitory Issues per 100k by State + Income by State + 100k Infections by Avg(Income) by State */ --Add in State Populations, Use Demo Database CREATE DATABASE IF NOT EXISTS DEMO; --Create Table for State Populations CREATE TABLE IF NOT EXISTS "DEMO"."PUBLIC"."SATE_POPULTATIONS" (STATE VARCHAR, POPULATION NUMBER); --2019 US Census Population Estimate by State --Source: https://www2.census.gov/programs-surveys/popest/tables/2010-2019/state/totals/nst-est2019-01.xlsx INSERT INTO "DEMO"."PUBLIC"."SATE_POPULTATIONS" VALUES ('Alabama',4903185), ('Alaska',731545), ('Arizona',7278717), ('Arkansas',3017804), ('California',39512223), ('Colorado',5758736), ('Connecticut',3565287), ('Delaware',973764), ('District of Columbia',705749), ('Florida',21477737), ('Georgia',10617423), ('Hawaii',1415872), ('Idaho',1787065), ('Illinois',12671821), ('Indiana',6732219), ('Iowa',3155070), ('Kansas',2913314), ('Kentucky',4467673), ('Louisiana',4648794), ('Maine',1344212), ('Maryland',6045680), ('Massachusetts',6892503), ('Michigan',9986857), ('Minnesota',5639632), ('Mississippi',2976149), ('Missouri',6137428), ('Montana',1068778), ('Nebraska',1934408), ('Nevada',3080156), ('New Hampshire',1359711), ('New Jersey',8882190), ('New Mexico',2096829), ('New York',19453561), ('North Carolina',10488084), ('North Dakota',762062), ('Ohio',11689100), ('Oklahoma',3956971), ('Oregon',4217737), ('Pennsylvania',12801989), ('Rhode Island',1059361), ('South Carolina',5148714), ('South Dakota',884659), ('Tennessee',6829174), ('Texas',28995881), ('Utah',3205958), ('Vermont',623989), ('Virginia',8535519), ('Washington',7614893), ('West Virginia',1792147), ('Wisconsin',5822434), ('Wyoming',578759); --Check the Data Insert SELECT * FROM "DEMO"."PUBLIC"."SATE_POPULTATIONS"; --Quality Controls People SELECT T1.STATE AS WUNDERMAN_STATE , T2.STATE AS POPULATIONS_STATE, T3.PROVINCE_STATE AS JHU_STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" T1 JOIN "DEMO"."PUBLIC"."SATE_POPULTATIONS" T2 ON T1.STATE = T2.STATE JOIN "STARSCHEMA_COVID19"."PUBLIC"."JHU_COVID_19" T3 ON T1.STATE = T3.PROVINCE_STATE GROUP BY 1,2,3 ORDER BY 1; --Average Age and COVID --+-----------------------------------------------------------------------------------------------+-- --Age by State SELECT AVG(AGE::DECIMAL(5,2)) AS APPROXIMATE_STATE_AVG_AGE, STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" WHERE TRY_TO_NUMBER(AGE) > 0 GROUP BY STATE ORDER BY APPROXIMATE_STATE_AVG_AGE DESC; --100k Infection Rate by Avg(Age) by State WITH casesByState AS ( SELECT SUM(JHU.CASES) AS CONFIRMED_CASES, JHU.PROVINCE_STATE AS STATE FROM "STARSCHEMA_COVID19"."PUBLIC"."JHU_COVID_19" JHU WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE($DATE_VARIABLE) AND JHU.CASE_TYPE='Confirmed' GROUP BY STATE), statePopulations AS ( SELECT STATE, POPULATION FROM "DEMO"."PUBLIC"."SATE_POPULTATIONS") , ageBySate AS ( SELECT AVG(AGE::DECIMAL(5,2)) AS APPROXIMATE_STATE_AVG_AGE, STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" WHERE TRY_TO_NUMBER(AGE) > 0 GROUP BY STATE) SELECT statePopulations.STATE, SUM(CONFIRMED_CASES)/SUM(POPULATION)*100000 AS CASES_PER_100K, AVG(ageBySate.APPROXIMATE_STATE_AVG_AGE) AS APPROXIMATE_STATE_AVG_AGE FROM casesByState JOIN statePopulations ON casesByState.STATE = statePopulations.STATE JOIN ageBySate ON casesByState.STATE = ageBySate.STATE GROUP BY statePopulations.STATE ORDER BY CASES_PER_100K DESC; --Respiratory Ailments and COVID --+-----------------------------------------------------------------------------------------------+-- -- Find State, Age-Range with highest numbers of individuals with any Respiratory Ailment. WITH respiratoryAilments AS ( SELECT KEY_ID, AGE, AGE_RANGE, AILMENT_ALLERGY_SINUS_NASAL, AILMENT_ASTHMA, AILMENT_BRONCHITIS_COPD_EMPHYSEMA, AILMENT_COUGH_AND_COLD, AILMENT_NASAL_CONGESTION, AILMENT2_BRONCHITIS_CHRONIC_BRONCHITIS, AILMENT2_EMPHYSEMA, AILMENT2_NASAL_ALLERGIES, STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_ALIMENTS" WHERE COALESCE( AILMENT2_ASTHMA,AILMENT2_BRONCHITIS_CHRONIC_BRONCHITIS,AILMENT2_COPD, AILMENT2_EMPHYSEMA,AILMENT2_SINUSITIS,AILMENT2_SNORING, AILMENT_ALLERGY_SINUS_NASAL,AILMENT_ASTHMA,AILMENT_BRONCHITIS_COPD_EMPHYSEMA, AILMENT_CHRONIC_BRONCHITIS,AILMENT_COUGH_AND_COLD,AILMENT_GROUP_RESPIRATORY, AILMENT_LUNG_DISEASE,AILMENT_RESPIRATORY_AILMENTS,AILMENT_SNORING) = 'Yes' ) SELECT STATE, AGE_RANGE, COUNT(*) * 100 AS APPROXIMATE_COUNT FROM respiratoryAilments WHERE AGE_RANGE IS NOT NULL GROUP BY STATE, AGE_RANGE HAVING APPROXIMATE_COUNT >= 1000 ORDER BY APPROXIMATE_COUNT DESC; --Respiratory Issues per 100k by State WITH respiratoryAilments AS ( SELECT COUNT(*) * 100 AS APPROXIMATE_COUNT_RESPIRATORY_AILMENTS, STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" WHERE COALESCE( AILMENT2_ASTHMA,AILMENT2_BRONCHITIS_CHRONIC_BRONCHITIS,AILMENT2_COPD, AILMENT2_EMPHYSEMA,AILMENT2_SINUSITIS,AILMENT2_SNORING, AILMENT_ALLERGY_SINUS_NASAL,AILMENT_ASTHMA,AILMENT_BRONCHITIS_COPD_EMPHYSEMA, AILMENT_CHRONIC_BRONCHITIS,AILMENT_COUGH_AND_COLD,AILMENT_GROUP_RESPIRATORY, AILMENT_LUNG_DISEASE,AILMENT_RESPIRATORY_AILMENTS,AILMENT_SNORING) = 'Yes' GROUP BY STATE), statePopulations AS ( SELECT STATE, POPULATION FROM "DEMO"."PUBLIC"."SATE_POPULTATIONS") SELECT statePopulations.STATE, SUM(APPROXIMATE_COUNT_RESPIRATORY_AILMENTS)/SUM(POPULATION)*100000 AS AILMENTS_PER_100K FROM respiratoryAilments JOIN statePopulations ON respiratoryAilments.STATE = statePopulations.STATE GROUP BY statePopulations.STATE ORDER BY AILMENTS_PER_100K DESC; --100k Infection Rate by 100k Respitory Issues Rate by State WITH respiratoryAilments AS ( SELECT COUNT(*) * 100 AS APPROXIMATE_COUNT_RESPIRATORY_AILMENTS, STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" WHERE COALESCE( AILMENT2_ASTHMA,AILMENT2_BRONCHITIS_CHRONIC_BRONCHITIS,AILMENT2_COPD, AILMENT2_EMPHYSEMA,AILMENT2_SINUSITIS,AILMENT2_SNORING, AILMENT_ALLERGY_SINUS_NASAL,AILMENT_ASTHMA,AILMENT_BRONCHITIS_COPD_EMPHYSEMA, AILMENT_CHRONIC_BRONCHITIS,AILMENT_COUGH_AND_COLD,AILMENT_GROUP_RESPIRATORY, AILMENT_LUNG_DISEASE,AILMENT_RESPIRATORY_AILMENTS,AILMENT_SNORING) = 'Yes' GROUP BY STATE), statePopulations AS ( SELECT STATE, POPULATION FROM "DEMO"."PUBLIC"."SATE_POPULTATIONS"), casesByState AS ( SELECT SUM(JHU.CASES) AS CONFIRMED_CASES, JHU.PROVINCE_STATE AS STATE FROM "STARSCHEMA_COVID19"."PUBLIC"."JHU_COVID_19" JHU WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE($DATE_VARIABLE) AND JHU.CASE_TYPE='Confirmed' GROUP BY STATE) SELECT statePopulations.STATE, SUM(APPROXIMATE_COUNT_RESPIRATORY_AILMENTS)/SUM(POPULATION)*100000 AS AILMENTS_PER_100K, SUM(CONFIRMED_CASES)/SUM(POPULATION)*100000 AS CASES_PER_100K FROM respiratoryAilments JOIN statePopulations ON respiratoryAilments.STATE = statePopulations.STATE JOIN casesByState ON respiratoryAilments.STATE = casesByState.STATE GROUP BY statePopulations.STATE ORDER BY AILMENTS_PER_100K DESC; --100k Death Rate by 100k Respitory Issues Rate by State WITH respiratoryAilments AS ( SELECT COUNT(*) * 100 AS APPROXIMATE_COUNT_RESPIRATORY_AILMENTS, STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" WHERE COALESCE( AILMENT2_ASTHMA,AILMENT2_BRONCHITIS_CHRONIC_BRONCHITIS,AILMENT2_COPD, AILMENT2_EMPHYSEMA,AILMENT2_SINUSITIS,AILMENT2_SNORING, AILMENT_ALLERGY_SINUS_NASAL,AILMENT_ASTHMA,AILMENT_BRONCHITIS_COPD_EMPHYSEMA, AILMENT_CHRONIC_BRONCHITIS,AILMENT_COUGH_AND_COLD,AILMENT_GROUP_RESPIRATORY, AILMENT_LUNG_DISEASE,AILMENT_RESPIRATORY_AILMENTS,AILMENT_SNORING) = 'Yes' GROUP BY STATE), statePopulations AS ( SELECT STATE, POPULATION FROM "DEMO"."PUBLIC"."SATE_POPULTATIONS"), deathsByState AS ( SELECT SUM(JHU.CASES) AS DEATHS, JHU.PROVINCE_STATE AS STATE FROM "STARSCHEMA_COVID19"."PUBLIC"."JHU_COVID_19" JHU WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE($DATE_VARIABLE) AND JHU.CASE_TYPE='Deaths' GROUP BY STATE) SELECT statePopulations.STATE, SUM(APPROXIMATE_COUNT_RESPIRATORY_AILMENTS)/SUM(POPULATION)*100000 AS AILMENTS_PER_100K, SUM(DEATHS)/SUM(POPULATION)*100000 AS DEATHS_PER_100K FROM respiratoryAilments JOIN statePopulations ON respiratoryAilments.STATE = statePopulations.STATE JOIN deathsByState ON respiratoryAilments.STATE = deathsByState.STATE GROUP BY statePopulations.STATE ORDER BY AILMENTS_PER_100K DESC; --Income and COVID --+-----------------------------------------------------------------------------------------------+-- --Income by State SELECT AVG(CASE ESTIMATED_HOUSEHOLD_INCOME_3_DOT_0 WHEN 'A - Less than $15,000' THEN 5000 WHEN 'B - $15,000-$19,999' THEN 15000 WHEN 'C - $20,000-$29,999' THEN 20000 WHEN 'D - $30,000-$39,999' THEN 30000 WHEN 'E - $40,000-$49,999' THEN 40000 WHEN 'F - $50,000-$59,999' THEN 50000 WHEN 'G - $60,000-$74,999' THEN 60000 WHEN 'H - $75,000-$99,999' THEN 75000 WHEN 'I - $100,000-$124,999' THEN 100000 WHEN 'J - $125,000-$149,999' THEN 125000 WHEN 'K - $150,000-$199,999' THEN 150000 WHEN 'L - $200,000-$249,999' THEN 200000 WHEN 'M - $250,000-$399,999' THEN 250000 WHEN 'N - $400,000-$499,999' THEN 400000 WHEN 'O - $500,000+' THEN 500000 ELSE 0 END) AS APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME, STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" GROUP BY STATE ORDER BY APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME DESC; --100k Infections by Avg(Income) by State --Graph Scatter with Infection Rate on Y-Axis and Avg(Income) on X-Axis WITH incomeByState AS ( SELECT AVG(CASE ESTIMATED_HOUSEHOLD_INCOME_3_DOT_0 WHEN 'A - Less than $15,000' THEN 5000 WHEN 'B - $15,000-$19,999' THEN 15000 WHEN 'C - $20,000-$29,999' THEN 20000 WHEN 'D - $30,000-$39,999' THEN 30000 WHEN 'E - $40,000-$49,999' THEN 40000 WHEN 'F - $50,000-$59,999' THEN 50000 WHEN 'G - $60,000-$74,999' THEN 60000 WHEN 'H - $75,000-$99,999' THEN 75000 WHEN 'I - $100,000-$124,999' THEN 100000 WHEN 'J - $125,000-$149,999' THEN 125000 WHEN 'K - $150,000-$199,999' THEN 150000 WHEN 'L - $200,000-$249,999' THEN 200000 WHEN 'M - $250,000-$399,999' THEN 250000 WHEN 'N - $400,000-$499,999' THEN 400000 WHEN 'O - $500,000+' THEN 500000 ELSE 0 END) AS APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME, STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" GROUP BY STATE), statePopulations AS ( SELECT STATE, POPULATION FROM "DEMO"."PUBLIC"."SATE_POPULTATIONS"), casesByState AS ( SELECT SUM(JHU.CASES) AS CONFIRMED_CASES, JHU.PROVINCE_STATE AS STATE FROM "STARSCHEMA_COVID19"."PUBLIC"."JHU_COVID_19" JHU WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE($DATE_VARIABLE) AND JHU.CASE_TYPE='Confirmed' GROUP BY STATE) SELECT statePopulations.STATE, SUM(CONFIRMED_CASES)/SUM(POPULATION)*100000 AS CASES_PER_100K, AVG(APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME) AS APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME FROM incomeByState JOIN statePopulations ON incomeByState.STATE = statePopulations.STATE JOIN casesByState ON incomeByState.STATE = casesByState.STATE GROUP BY statePopulations.STATE ORDER BY APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME DESC; --100k Deaths by Avg(Income) by State --Graph Scatter with Death Rate on Y-Axis and Avg(Income) on X-Axis WITH incomeByState AS ( SELECT AVG(CASE ESTIMATED_HOUSEHOLD_INCOME_3_DOT_0 WHEN 'A - Less than $15,000' THEN 5000 WHEN 'B - $15,000-$19,999' THEN 15000 WHEN 'C - $20,000-$29,999' THEN 20000 WHEN 'D - $30,000-$39,999' THEN 30000 WHEN 'E - $40,000-$49,999' THEN 40000 WHEN 'F - $50,000-$59,999' THEN 50000 WHEN 'G - $60,000-$74,999' THEN 60000 WHEN 'H - $75,000-$99,999' THEN 75000 WHEN 'I - $100,000-$124,999' THEN 100000 WHEN 'J - $125,000-$149,999' THEN 125000 WHEN 'K - $150,000-$199,999' THEN 150000 WHEN 'L - $200,000-$249,999' THEN 200000 WHEN 'M - $250,000-$399,999' THEN 250000 WHEN 'N - $400,000-$499,999' THEN 400000 WHEN 'O - $500,000+' THEN 500000 ELSE 0 END) AS APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME, STATE FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" GROUP BY STATE), statePopulations AS ( SELECT STATE, POPULATION FROM "DEMO"."PUBLIC"."SATE_POPULTATIONS"), casesByState AS ( SELECT SUM(JHU.CASES) AS DEATHS, JHU.PROVINCE_STATE AS STATE FROM "STARSCHEMA_COVID19"."PUBLIC"."JHU_COVID_19" JHU WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE($DATE_VARIABLE) AND JHU.CASE_TYPE='Deaths' GROUP BY STATE) SELECT statePopulations.STATE, SUM(DEATHS)/SUM(POPULATION)*100000 AS DEATHS_PER_100K, AVG(APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME) AS APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME FROM incomeByState JOIN statePopulations ON incomeByState.STATE = statePopulations.STATE JOIN casesByState ON incomeByState.STATE = casesByState.STATE GROUP BY statePopulations.STATE ORDER BY APPROXIMATE_STATE_AVG_ESTIMATED_HOUSEHOLD_INCOME DESC; --Desired Future SQL --States Ready to Breakout based on Recovery Index(Pent up Financial Demand) --What's in the Data? DESC TABLE "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT"; SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "name" LIKE '%INCOME%'; SELECT * FROM "WUNDERMAN"."DOD_DECODE_SAMPLE_CURRENT"."DECODE_STD_ROYALTY_PREMIUM_PLUS_ALIMENT" LIMIT 10;

Conclusion
As you can see for yourself the Snowflake Data Marketplace is an amazing place where data enthusiasts are coming together to further enhance data as actionable and meaningful to your narrative. With just clicks of a mouse, we were able to query 4 different data sources with COVID-19 cases, weather, population and demographic insights across the globe. I highly encourage playing around in the marketplace. Happy (Data Marketplace) Querying!