Actuals vs. Target Calculations for Shopify data in Redshift

gistfile1.txt

WITH
  actuals AS (
  SELECT
    shopify_shop,
    CASE
      WHEN billing_address__country_code = 'GB' THEN 'UK'
      WHEN billing_address__country_code = 'US' THEN 'US'
    ELSE
    'RoW'
  END
    AS billing_address__country_code,
    to_DATE(CONCAT(to_char(DATE_TRUNC('day', created_at),
          'DD/MM/YYYY'),' 00:00:00'),
      'DD-MM-YYYY HH24:MI:SS') AS actual_day,
    SUM(total_price_usd) AS actual_value
  FROM
    shopify.orders
  GROUP BY
    1,
    2,
    3),
  targets AS (
  SELECT
    shopify_shop,
    billing_address__country_code,
    target_day,
    daily_target
  FROM
    lbw_targets.daily_targets2)
SELECT
  current_week.week_ending,
  current_week.shopify_shop,
  current_week.billing_address__country_code,
  current_week.actual_value_mtd,
  current_week.target_value_mtd,
  previous_week.actual_value_mtd AS previous_week_actual_value_mtd,
  previous_week.target_value_mtd AS previous_week_target_value_mtd,
  current_week.actual_value_mtd/current_week.target_value_mtd AS pct_mtd_to_target,
  previous_week.actual_value_mtd/previous_week.target_value_mtd AS previous_week_pct_mtd_to_target,
  (current_week.actual_value_mtd/current_week.target_value_mtd)/(previous_week.actual_value_mtd/previous_week.target_value_mtd) AS mtd_vs_prev_week_mtd
FROM (
  SELECT
    a.*,
    EXTRACT(MONTH
    FROM
      actual_day) AS actual_month,
    DATE_TRUNC('week',actual_day) AS week_ending,
    t.daily_target,
    SUM(a.actual_value) OVER (PARTITION BY a.shopify_shop, a.billing_address__country_code, EXTRACT(MONTH FROM actual_day)
    ORDER BY
      actual_day) AS actual_value_mtd,
    SUM(t.daily_target) OVER (PARTITION BY a.shopify_shop, a.billing_address__country_code, EXTRACT(MONTH FROM actual_day)
    ORDER BY
      actual_day) AS target_value_mtd
  FROM
    actuals a
  JOIN
    targets t
  ON
    a.billing_address__country_code = t.billing_address__country_code
    AND a.actual_day = t.target_day
    AND a.shopify_shop = t.shopify_shop
  ORDER BY
    a.shopify_shop,
    a.billing_address__country_code,
    EXTRACT(MONTH
    FROM
      actual_day),
    actual_day) current_week
LEFT OUTER JOIN (
  SELECT
    a.*,
    EXTRACT(MONTH
    FROM
      actual_day) AS actual_month,
    DATE_TRUNC('week',actual_day) AS week_ending,
    t.daily_target,
    SUM(a.actual_value) OVER (PARTITION BY a.shopify_shop, a.billing_address__country_code, EXTRACT(MONTH FROM actual_day)
    ORDER BY
      actual_day) AS actual_value_mtd,
    SUM(t.daily_target) OVER (PARTITION BY a.shopify_shop, a.billing_address__country_code, EXTRACT(MONTH FROM actual_day)
    ORDER BY
      actual_day) AS target_value_mtd
  FROM
    actuals a
  JOIN
    targets t
  ON
    a.billing_address__country_code = t.billing_address__country_code
    AND a.actual_day = t.target_day
    AND a.shopify_shop = t.shopify_shop
  ORDER BY
    a.shopify_shop,
    a.billing_address__country_code,
    EXTRACT(MONTH
    FROM
      actual_day),
    actual_day) previous_week
ON
  previous_week.actual_day = current_week.actual_day + INTERVAL '-7' DAY
  AND previous_week.shopify_shop = current_week.shopify_shop
  AND previous_week.billing_address__country_code = current_week.billing_address__country_code
WHERE
  current_week.week_ending = current_week.actual_day::timestamp