WITH

reinstall AS (
    SELECT DISTINCT uid
    FROM stat.funnelTrack
    WHERE dt >= '2023-06-01'
        AND action IN (
            'reinstall_detected',
            'migration_gmd_received',
            'migration_success_show'
        )
        OR (action IN ('bind_user', 'rebind_user')
            AND visitParamExtractString(addJson, 'reason') IN ('moveGlobalToRu', 'migration', 'adid'))
),

rebind_user AS (
    SELECT DISTINCT device_id AS uid
    FROM billing.a_device_event
    WHERE ts >= '2023-06-01'
        AND type = 'rebind_user'
),

exp_users AS (
    SELECT
        ft.uid as uid,
        MAX(visitParamExtractString(ft.addJson, 'option')) AS exp_group
    FROM
        stat.funnelTrack AS ft
    LEFT ANTI JOIN reinstall AS ri ON ft.uid = ri.uid
    LEFT ANTI JOIN rebind_user AS rb ON ft.uid = rb.uid
    WHERE
        ft.dt >= '2024-05-01'
        AND ft.action = 'pteam_experiment_stop_places_on_map'
    GROUP BY
        ft.uid
    HAVING exp_group in ('old', 'new')
),

payments AS (
SELECT toDate(ContractCharge_TM) AS PaymentDate,
       uid,
       SUM(USDNet) AS TotalUSDNet,
       SUM(USDNet) > 0 AS TotalPurchase,
       SUM(IF(concat(Type, Value) IN ('purchase_minutes_1', 'purchase_minutes_180', 'purchase_minutes_30', 'purchase_minutes_90', 'subscription_minutes_30', 'subscription_minutes_365', 'subscription_minutes_7'), USDNet, 0)) AS MinutesUSDNet,
       SUM(IF(USDNet > 0
              AND concat(Type, Value) IN ('purchase_minutes_1', 'purchase_minutes_180', 'purchase_minutes_30', 'purchase_minutes_90', 'subscription_minutes_30', 'subscription_minutes_365', 'subscription_minutes_7'), 1, 0)) AS MinutesPurchase,
       SUM(IF(concat(Type, Value) IN ('subscription_30', 'subscription_365'), USDNet, 0)) AS SubscriptionUSDNet,
       SUM(IF(concat(Type, Value) IN ('subscription_30', 'subscription_365'), 1, 0)) AS SubscriptionPurchase,
       SUM(IF(Sku LIKE '%care_plus%' OR Sku LIKE '%premium%', USDNet, 0)) AS PlusSubscriptionUSDNet,
       SUM(IF(Sku LIKE '%care_plus%' OR Sku LIKE '%premium%', 1, 0)) AS PlusSubscriptionPurchase
FROM analytics.money join exp_users using(uid)
WHERE ChargeNumber >= 1
  AND NOT was_refunded
  AND USDNet >= 0.02
  AND ContractCharge_TM >= '2024-01-01'
GROUP BY PaymentDate, uid
),

funnelTrack_RawEvents AS (
SELECT
    dt AS EventDate,
    uid,
    countIf(action = 'open_function_zones') AS open_function_zones,
    countIf(action = 'zones_screen') AS zones_screen,
    countIf(action = 'zones_place_create') AS zones_place_create,
    countIf(action = 'zones_pingo_popup_shown') AS zones_pingo_popup_shown,
    countIf(action = 'zones_place_create_screen') AS zones_place_create_screen,
    countIf(action = 'zones_place_create_click') AS zones_place_create_click,
    countIf(action = 'zones_place_create_done') AS zones_place_create_done,
    countIf(action = 'map_page_banner') AS map_page_banner
FROM
    stat.funnelTrack join exp_users using(uid)
WHERE
    dt >= '2024-05-01' AND dt < today()
    AND action IN ('open_function_zones', 'zones_screen', 'zones_place_create',
                   'zones_pingo_popup_shown', 'zones_place_create_screen', 'zones_place_create_click',
                   'zones_place_create_done', 'map_page_banner')
GROUP BY
    EventDate, uid
),

events AS (
    SELECT
        e.uid as uid,
        toDate(p.full_reg_datetime) AS FullRegDatetime,
        toDate(e.dt) AS EventDate,
        notEmpty(e.open) AS Open,
        p.platform AS Platform,
        p.region AS Region,
        p.ip_country AS IPCountry
    FROM analytics.parent_actions e
    JOIN analytics.parent_properties p on e.uid = p.uid
    JOIN exp_users a on e.uid = a.uid
    WHERE e.dt >= '2024-05-01'
          AND p.full_reg_datetime >= '2000-01-01'
          AND p.is_full_register
          AND p.uid IS NOT NULL
)

SELECT
    e1.Platform AS Platform,
    e1.Region AS Region,
    eu.exp_group AS exp_group,
    toDate(e1.FullRegDatetime) AS RegistrationDate,
    if(dateDiff('day', e1.FullRegDatetime, e1.EventDate) < 0, 0, dateDiff('day', e1.FullRegDatetime, e1.EventDate)) AS CohortDayLife,
    uniqExact(e1.uid) AS OpenUsers,
    -- Fact user metrics (core)
    SUM(e1.Open) AS OpenApp,
    SUM(cm.open_function_zones) AS OpenFuncZones,
    SUM(cm.zones_screen) AS ZonesScreen,
    SUM(cm.zones_place_create) AS ZonesPlaceCreate,
    SUM(cm.zones_place_create_screen) AS ZonesPlaceCreateScreen,
    SUM(cm.zones_place_create_click) AS ZonesPlaceCreateClick,
    SUM(cm.zones_place_create_done) AS ZonesPlaceCreateDone,
    SUM(cm.zones_pingo_popup_shown) AS ZonesPingoPopup,
    SUM(cm.map_page_banner) AS OpenMapPageBanner,

    -- Real-time user metrics (core)
    SUM(IF(e1.Open AND e2.Open, 1, NULL)) AS RTOpenUsers,
    SUM(IF(cm.map_page_banner AND cm1.map_page_banner, 1, NULL)) AS RTOpenMapPageBannerUsers,

    -- Purchase metrics
    SUM(payments.TotalUSDNet) AS TotalUSDNet,
    SUM(payments.TotalPurchase) AS TotalPurchase,
    SUM(payments.MinutesUSDNet) AS MinutesUSDNet,
    SUM(payments.MinutesPurchase) AS MinutesPurchase,
    SUM(payments.SubscriptionUSDNet) AS SubscriptionUSDNet,
    SUM(payments.SubscriptionPurchase) AS SubscriptionPurchase,
    SUM(payments.PlusSubscriptionUSDNet) AS PlusSubscriptionUSDNet,
    SUM(payments.PlusSubscriptionPurchase) AS PlusSubscriptionPurchase
FROM exp_users AS eu
LEFT JOIN events e1 ON eu.uid = e1.uid
LEFT JOIN events e2 ON e1.uid = e2.uid AND e2.EventDate = e1.EventDate + INTERVAL 1 DAY
LEFT JOIN payments ON payments.uid = e1.uid AND payments.PaymentDate = e1.EventDate
LEFT JOIN funnelTrack_RawEvents cm ON cm.uid = e1.uid AND cm.EventDate = e1.EventDate
LEFT JOIN funnelTrack_RawEvents cm1 ON cm.uid = cm1.uid AND cm.EventDate = cm1.EventDate + INTERVAL 1 DAY
GROUP BY 1, 2, 3, 4, 5 ORDER BY CohortDayLife, Platform, Region, exp_group