Pasted

-- ───────────────
-- DOWNLOADS ISOLATED
-- ───────────────
SELECT view_definition
FROM information_schema.views
WHERE TABLE_NAME = 'downloads_isolated'
 
CREATE OR REPLACE VIEW downloads_isolated AS
 
WITH
 
-- 1) Base list of download uplifts
du AS (
  SELECT app_id, start_date, num_days
  FROM downloads_uplifts
  WHERE (ww_period_units / num_days) > 10000 -- Filter out small datapoints with fewer than 10,000 daily downloads
),
 
-- 2) Per‐country pre/post averages & period sum (units)
per_country AS (
  SELECT
    du.app_id,
    du.start_date,
    du.num_days,
    rr.country,
    -- avg daily units before uplift
    SUM(rr.unified_units) FILTER (
      WHERE CAST(rr.DATE AS DATE)
        BETWEEN du.start_date - du.num_days * INTERVAL '1 day'
            AND du.start_date - INTERVAL '1 day'
    ) AS sum_pre,
    -- avg daily units during uplift
    SUM(rr.unified_units) FILTER (
      WHERE CAST(rr.DATE AS DATE)
        BETWEEN du.start_date
            AND du.start_date + (du.num_days - 1) * INTERVAL '1 day'
    ) AS sum_period,
    -- total units during uplift
    SUM(rr.unified_units) FILTER (
      WHERE CAST(rr.DATE AS DATE)
        BETWEEN du.start_date + (du.num_days) * INTERVAL '1 day'
            AND du.start_date + (2*du.num_days - 1) * INTERVAL '1 day'
    ) AS sum_post
  FROM du
  JOIN revenue_raw AS rr
    ON rr.app_id = du.app_id
  GROUP BY du.app_id, du.start_date, du.num_days, rr.country
),
 
-- 3) Global total units during uplift (WW only)
global_period AS (
  SELECT
    du.app_id,
    du.start_date,
    du.num_days,
    SUM(rr.unified_units) AS ww_sum_period
  FROM du
  JOIN revenue_raw AS rr
    ON rr.app_id  = du.app_id
   AND rr.country = 'WW'                                           -- ← only WW
   AND CAST(rr.DATE AS DATE)
       BETWEEN du.start_date
           AND du.start_date + (du.num_days - 1) * INTERVAL '1 day'
  GROUP BY du.app_id, du.start_date, du.num_days
),
 
-- 4) Collect any “new region” codes
region_issues AS (
  SELECT
    pc.app_id,
    pc.start_date,
    pc.num_days,
    group_concat(pc.country, ',') AS region_list
  FROM per_country pc
  JOIN global_period gp
    USING (app_id, start_date, num_days)
  WHERE pc.sum_pre   = 0
    AND pc.sum_period >= 0.05 * gp.ww_sum_period  -- Make sure it's at least 5% of downloads
  GROUP BY pc.app_id, pc.start_date, pc.num_days
),
 
-- 5) Per‐platform sums & pre‐period sums (WW only)
per_platform AS (
  SELECT
    du.app_id,
    du.start_date,
    du.num_days,
 
    -- WW iOS units
    SUM(rr.ipad_units + rr.iphone_units) FILTER (
      WHERE CAST(rr.DATE AS DATE)
        BETWEEN du.start_date - du.num_days * INTERVAL '1 day'
            AND du.start_date - INTERVAL '1 day'
    ) AS sum_pre_ios,
    SUM(rr.ipad_units + rr.iphone_units) FILTER (
      WHERE CAST(rr.DATE AS DATE)
        BETWEEN du.start_date
            AND du.start_date + (du.num_days - 1) * INTERVAL '1 day'
    ) AS sum_ios,
 
    -- WW Android units
    SUM(rr.android_units) FILTER (
      WHERE CAST(rr.DATE AS DATE)
        BETWEEN du.start_date - du.num_days * INTERVAL '1 day'
            AND du.start_date - INTERVAL '1 day'
    ) AS sum_pre_android,
    SUM(rr.android_units) FILTER (
      WHERE CAST(rr.DATE AS DATE)
        BETWEEN du.start_date
            AND du.start_date + (du.num_days - 1) * INTERVAL '1 day'
    ) AS sum_android
 
  FROM du
  JOIN revenue_raw AS rr
    ON rr.app_id  = du.app_id
   AND rr.country = 'WW'                                           -- ← only WW
  GROUP BY du.app_id, du.start_date, du.num_days
),
 
-- 6) Collect any “new platform” labels (sum-only version)
platform_issues AS (
  SELECT
    pp.app_id,
    pp.start_date,
    pp.num_days,
    GROUP_CONCAT(
      CASE
        WHEN pp.sum_pre_ios      = 0
         AND pp.sum_ios          >= 0.05 * (pp.sum_ios + pp.sum_android)
        THEN 'iOS'
        WHEN pp.sum_pre_android  = 0
         AND pp.sum_android      >= 0.05 * (pp.sum_ios + pp.sum_android)
        THEN 'Android'
      END,
    ',') AS platform_list
  FROM per_platform pp
  WHERE (pp.sum_pre_ios     = 0 AND pp.sum_ios     >= 0.05 * (pp.sum_ios + pp.sum_android))
     OR (pp.sum_pre_android = 0 AND pp.sum_android >= 0.05 * (pp.sum_ios + pp.sum_android))
  GROUP BY pp.app_id, pp.start_date, pp.num_days
)
 
 
-- 7) All uplifts + rejection flags
SELECT
  du.app_id,
  du.start_date,
  du.num_days,
  COALESCE(ri.region_list,   '') AS region_rejected,
  COALESCE(pi.platform_list, '') AS platform_rejected
FROM downloads_uplifts AS du
LEFT JOIN region_issues   AS ri USING (app_id, start_date, num_days)
LEFT JOIN platform_issues AS pi USING (app_id, start_date, num_days)
ORDER BY du.app_id, du.start_date;