Calculated Field / Snowflake / SnowSQL

Snowflake: Hands-on with the Snowflake Data Marketplace, Wunderman and COVID-19

Posted by

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;
Output of the Last Query in the Starschema Query Block

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';
Output of the Last Query in the Safegraph Query Block

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;
Output of the Last Query in the Weathersource Query Block

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.

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;
Output of the Last Query in the Wunderman Query Block

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!

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s