Some useful queries

Roser normalisation

Cases per million over days since first case:

-- Roser normalisation
-- (Querying cases per million and appending cases since first confirmed case in geography)
-- cvcb.27APR2020: created 

SELECT a.*
FROM (SELECT cbd.ISO3166_1,
             cbd.DATE,
             cbd.TOTAL_POP,
             cbd.CASES,
             cbd.CASES_PER_MILLION,
             cbd.DATE - mdb.FIRST_INSTANCE AS DAYS_SINCE_FIRST_CASE
      FROM (SELECT case_count.ISO3166_1,
                   case_count.DATE,
                   demographics.TOTAL_POP,
                   case_count.CASES,
                   (1000000 * case_count.CASES) / demographics.TOTAL_POP AS CASES_PER_MILLION
            FROM (SELECT ISO3166_1, DATE, SUM(CASES) AS CASES
                  FROM "COVID19"."PUBLIC"."JHU_COVID_19"
                  WHERE CASE_TYPE = 'Confirmed'
                  GROUP BY ISO3166_1, DATE) case_count
                     LEFT OUTER JOIN
                 (SELECT ISO3166_1, SUM(TOTAL_POPULATION) AS TOTAL_POP
                  FROM "COVID19"."PUBLIC"."DATABANK_DEMOGRAPHICS"
                  GROUP BY ISO3166_1) demographics
                 ON demographics.ISO3166_1 = case_count.ISO3166_1) cbd
               LEFT JOIN
           (SELECT ISO3166_1, MIN(DATE) AS FIRST_INSTANCE
            FROM "COVID19"."PUBLIC"."JHU_COVID_19"
            WHERE CASE_TYPE = 'Confirmed'
              AND CASES > 0
            GROUP BY ISO3166_1) mdb
           ON cbd.ISO3166_1 = mdb.ISO3166_1) a
WHERE a.CASES > 0;

Cross-query mobility data

Requires QL2_FREEMIUM. Queries mobility data and crosstabulates it against hotel rates.

-- Joins average price of hotels with mobility data
-- cvcb.24APR2020

SELECT RATES.STATE,
       RATES.QUERY_DATE AS DATE,
       RATES.STARS,
       RATES.MEAN_TOTAL_RATE,
       MOBILITY.GROCERY_AND_PHARMACY_CHANGE_PERC,
       MOBILITY.PARKS_CHANGE_PERC,
       MOBILITY.RESIDENTIAL_CHANGE_PERC,
       MOBILITY.RETAIL_AND_RECREATION_CHANGE_PERC,
       MOBILITY.TRANSIT_STATIONS_CHANGE_PERC,
       MOBILITY.WORKPLACES_CHANGE_PERC
FROM (SELECT STATE, COUNTRY, TO_DATE(QTS) AS QUERY_DATE, STARS, AVG(TOTAL_RATE) AS MEAN_TOTAL_RATE
      FROM "QL2_FREEMIUM"."PUBLIC"."DET_HOTELS_SWSHARE"
      WHERE COUNTRY = 'US'
        AND TO_DATE(QTS) >= '2020-01-31'
        AND STARS IS NOT NULL
      GROUP BY STATE, COUNTRY, QUERY_DATE, STARS) RATES
         INNER JOIN
     (SELECT ISO_3166_1                             AS COUNTRY,
             ISO_3166_2                             AS STATE,
             DATE,
             AVG(GROCERY_AND_PHARMACY_CHANGE_PERC)  AS GROCERY_AND_PHARMACY_CHANGE_PERC,
             AVG(PARKS_CHANGE_PERC)                 AS PARKS_CHANGE_PERC,
             AVG(RESIDENTIAL_CHANGE_PERC)           AS RESIDENTIAL_CHANGE_PERC,
             AVG(RETAIL_AND_RECREATION_CHANGE_PERC) AS RETAIL_AND_RECREATION_CHANGE_PERC,
             AVG(TRANSIT_STATIONS_CHANGE_PERC)      AS TRANSIT_STATIONS_CHANGE_PERC,
             AVG(WORKPLACES_CHANGE_PERC)            AS WORKPLACES_CHANGE_PERC
      FROM "COVID19"."PUBLIC"."GOOG_GLOBAL_MOBILITY_REPORT"
      WHERE ISO_3166_1 = 'US'
        AND ISO_3166_2 IS NOT NULL
      GROUP BY COUNTRY, STATE, DATE) MOBILITY
     ON RATES.STATE = MOBILITY.STATE AND RATES.COUNTRY = MOBILITY.COUNTRY AND RATES.QUERY_DATE = MOBILITY.DATE;

CFR calculation

Calculates rolling case-fatality ratio.

-- Case Fatality Ratio calculation
-- cvcb.28APR2020: created 

SELECT mortality.ISO3166_1,
       mortality.ISO3166_2,
       mortality.DATE,
       morbidity.CASES,
       mortality.DEATHS,
       mortality.DEATHS / morbidity.CASES AS CFR
FROM (SELECT ISO3166_1, ISO3166_2, DATE, SUM(CASES) AS CASES
      FROM "COVID19"."PUBLIC"."JHU_COVID_19"
      WHERE CASE_TYPE = 'Confirmed'
        AND CASES > 0
      GROUP BY ISO3166_1, ISO3166_2, DATE) morbidity
         LEFT JOIN
     (SELECT ISO3166_1, ISO3166_2, DATE, SUM(CASES) AS DEATHS
      FROM "COVID19"."PUBLIC"."JHU_COVID_19"
      WHERE CASE_TYPE = 'Deaths'
      GROUP BY ISO3166_1, ISO3166_2, DATE) mortality
     ON morbidity.ISO3166_1 = mortality.ISO3166_1 AND morbidity.ISO3166_2 = mortality.ISO3166_2 AND
        morbidity.DATE = mortality.DATE;