Covid

DEFINE MACRO trailing_average
  AVG($1)
    OVER (
      PARTITION BY country_code
      ORDER BY UNIX_DATE(date)
      RANGE BETWEEN $2 PRECEDING AND $3
    ) AS avg_$1_$4;

DEFINE MACRO time_metrics
  $trailing_average($1, 6, CURRENT ROW, cw),
  $trailing_average($1, 15, 7 PRECEDING, lw),
  $trailing_average($1, 60, 30 PRECEDING, lm),
  $trailing_average($1, 90, 60 PRECEDING, l2m);

DEFINE MACRO select_time
  avg_$1_cw,
  avg_$1_lw,
  avg_$1_lm,
  avg_$1_l2m;

DEFINE MACRO rev_weighted
  SUM(avg_$1_cw * revenue) / SUM(revenue) AS avg_$1_rev_weighted_cw,
  SUM(avg_$1_lw * revenue) / SUM(revenue) AS avg_$1_rev_weighted_lw,
  SUM(avg_$1_lm * revenue) / SUM(revenue) AS avg_$1_rev_weighted_lm,
  SUM(avg_$1_l2m * revenue) / SUM(revenue) AS avg_$1_rev_weighted_l2m;

DEFINE MACRO pop_weighted
  SAFE_DIVIDE(SUM(avg_$1_cw), SUM(population)) * $2 AS avg_$1_$3_cw,
  SAFE_DIVIDE(SUM(avg_$1_lw), SUM(population)) * $2 AS avg_$1_$3_lw,
  SAFE_DIVIDE(SUM(avg_$1_lm), SUM(population)) * $2 AS avg_$1_$3_lm,
  SAFE_DIVIDE(SUM(avg_$1_l2m), SUM(population)) * $2 AS avg_$1_$3_l2m;

DEFINE MACRO create_report
  WITH
    AggCovidData AS (
      SELECT
        CAST(date AS DATE) AS date,
        region,
        subregion,
        a.country_code AS country_code,
        country_name,
        stringency_index,
        retail_and_recreation_mobility AS retail_mobility,
        total_persons_vaccinated,
        total_persons_fully_vaccinated,
        new_confirmed_cases AS new_daily_cases,
        total_confirmed_cases,
        new_deaths,
        total_deaths,
        population,
        revenue
      FROM finance_analysts.GlobalCovidDasnav a
      INNER JOIN
        (
          SELECT service_country_code AS country_code, SUM(revenue) AS revenue
          FROM finance_analysts.GlobalDasnav
          WHERE date > '2021-01-01'
          GROUP BY 1
        ) b
        ON b.country_code = a.country_code  -- Each country code weighted by their YTD Revenue
      LEFT JOIN
        (
          SELECT country_code2, subregion_name AS subregion, region_name AS region
          FROM Ads_Countries_D
          GROUP BY 1, 2, 3
        ) AS c
        ON a.country_code = c.country_code2
      WHERE
        date > '2020-01-01'
        AND population > 1e5
    ),
    time_series AS (
      SELECT
        *,
        $time_metrics(stringency_index),
        $time_metrics(retail_mobility),
        $time_metrics(total_persons_vaccinated),
        $time_metrics(new_daily_cases),
        $time_metrics(total_confirmed_cases),
        $time_metrics(new_deaths),
        $time_metrics(total_deaths)
      FROM AggCovidData
    ),
    pre_rev_weighted AS (
      SELECT
        date,
        region,
        subregion,
        country_code,
        country_name,
        population,
        revenue,
        total_confirmed_cases,
        new_deaths,
        total_deaths,
        $select_time(stringency_index),
        $select_time(retail_mobility),
        $select_time(total_persons_vaccinated),
        $select_time(new_daily_cases),
        $select_time(total_confirmed_cases),
        $select_time(new_deaths),
        $select_time(total_deaths)
      FROM time_series
    )
  SELECT
    date,
    $1 AS geo_type,
    $2 AS geo,
    IF($1 = 'country_name', country_code, NULL) AS country_code,
    SUM(population) AS population,
    SUM(revenue) AS revenue,
    SUM(new_deaths) AS new_deaths,
    SUM(total_deaths) AS total_deaths,
    $rev_weighted(stringency_index),
    $rev_weighted(retail_mobility),
    $pop_weighted(total_persons_vaccinated, 1, percentage),
    $pop_weighted(new_daily_cases, 100000, per100k),
    $pop_weighted(total_confirmed_cases, 100000, per100k),
    $pop_weighted(new_deaths, 100000, per100k),
    $pop_weighted(total_deaths, 100000, per100k),
    (SUM(avg_total_persons_vaccinated_cw * revenue) / SUM(revenue * population))
      AS avg_revweighted_total_persons_vaccinated_percentage_cw  -- Revenue weighted vaccination rate
  FROM pre_rev_weighted
  GROUP BY 1, 2, 3, 4;

CREATE OR REPLACE TABLE finance_analysts.covidstats
AS (
  $create_report('region', region)
  UNION ALL
  ($create_report('subregion',subregion))
  UNION ALL
  ($create_report('country_name',country_name))
);