Downloads Isolated Query

sir_pinski

May 6th, 2025

487

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

SQL 4.78 KB | None | 0 0

raw download clone embed print report

-- ───────────────
-- 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;

Tags: Naavik

Add Comment

Please, Sign In to add comment