WITH
open_action_counts AS (
    SELECT
        ft.uid,
        uniqExact(IF(action = 'open_settings_menu', dt, NULL)) AS open_settings,
        uniqExact(IF(action = 'open_add_family', dt, NULL)) AS open_family,
        uniqExact(IF(action = 'chat_screen', dt, NULL)) AS open_chat
    FROM
        stat.funnelTrack AS ft
    JOIN
        analytics.parent_properties AS pp
        ON ft.uid = pp.uid
    WHERE
        ft.dt >= '2025-01-01'
        AND ft.dt < '2025-02-01'
        AND pp.full_reg_datetime >= '2025-01-01'
        AND pp.full_reg_datetime < '2025-01-28'
        AND ft.action IN ('open_settings_menu', 'open_add_family', 'chat_screen')
        AND ft.ts BETWEEN pp.full_reg_datetime AND pp.full_reg_datetime + INTERVAL '2 days'
    GROUP BY
        ft.uid
),
user_activity AS (
    SELECT
        pa.uid,
        COUNT(*) AS days,
        SUM(LENGTH(open) + LENGTH(open_first)) AS sessions,
        SUM(notEmpty(open)) AS open_actions,
        SUM(notEmpty(open_parent_activity)) AS open_parent_activity,
        SUM(notEmpty(open_function_records)) AS open_func_records,
        SUM(notEmpty(listen_live_clicked)) AS listen_live_clicks,
        SUM(notEmpty(listen_live_started)) AS listen_live_starts,
        SUM(notEmpty(open_function_noise)) AS open_func_noise,
        SUM(notEmpty(noise_sent)) AS noise_sent_clicks,
        SUM(notEmpty(noise_requested)) AS noise_requests,
        SUM(notEmpty(open_function_zones)) AS open_func_zones,
        SUM(notEmpty(zones_place_create)) AS zones_place_creates,
        SUM(notEmpty(zones_place_create_done)) AS zones_place_creates_done,
        SUM(notEmpty(open_function_appstat)) AS open_func_appstat
    FROM
        analytics.parent_actions AS pa
    JOIN
        analytics.parent_properties AS pp
        ON pa.uid = pp.uid
    WHERE
        pa.dt >= '2025-01-01'
        AND pa.dt < '2025-02-01'
        AND pp.full_reg_datetime >= '2025-01-01'
        AND pp.full_reg_datetime < '2025-01-28'
        AND pa.dt BETWEEN pp.full_reg_datetime::date AND pp.full_reg_datetime::date + INTERVAL '2' DAY
    GROUP BY
        pa.uid
),
recent_payments AS (
    SELECT
        uid,
        MIN(dt) AS paid_from_date,
        MAX(PaidTill)::date AS paid_till_date,
        MAX(Tag LIKE '%care_plus%' OR Tag LIKE '%premium%') AS flg_premium
    FROM
        analytics.money
    WHERE
        ChargeNumber >= 1
        AND was_refunded != 1
        AND CONCAT(Type, Value) IN ('subscription_30', 'subscription_365')
        AND USDGross >= 0.25
        AND dt >= '2024-01-01'
        AND dt >= date_trunc('week', today()) - INTERVAL '30 day'
    GROUP BY
        uid
),
metrics AS (
    SELECT
        pp.region,
        pp.platform,
        pp.full_reg_datetime,
        pp.uid as user_uid,
        CASE
            WHEN child_android_cnt > 0 AND child_ios_cnt > 0 THEN 'both'
            WHEN child_android_cnt > 0 THEN 'android'
            WHEN child_ios_cnt > 0 THEN 'ios'
            WHEN child_watch_cnt > 0 THEN 'watch'
            ELSE 'other'
        END AS child_group,
        ua.days,
        ua.sessions,
        ua.open_actions,
        ua.open_parent_activity,
        ua.open_func_records,
        ua.listen_live_clicks,
        ua.listen_live_starts,
        ua.open_func_noise,
        ua.noise_sent_clicks,
        ua.noise_requests,
        ua.open_func_zones,
        ua.zones_place_creates,
        ua.zones_place_creates_done,
        ua.open_func_appstat,
        oac.open_settings,
        oac.open_family,
        oac.open_chat,
        CASE
            WHEN ua.days BETWEEN 29 AND 30 THEN 'F. 29-30 days'
            WHEN ua.days BETWEEN 22 AND 28 THEN 'E. 22-28 days'
            WHEN ua.days BETWEEN 15 AND 21 THEN 'D. 15-21 days'
            WHEN ua.days BETWEEN 8 AND 14 THEN 'C. 8-14 days'
            WHEN ua.days BETWEEN 3 AND 7 THEN 'B. 3-7 days'
            WHEN ua.days BETWEEN 1 AND 2 THEN 'A. 1-2 days'
            ELSE 'Other'
        END AS activity_group,
--        ROUND(ua.sessions / NULLIF(ua.days, 0), 0) AS avg_sessions_per_day,
        CASE
            WHEN dateDiff('day', pp.full_reg_datetime, now()) < 7 THEN 'A. 1 week'
            WHEN dateDiff('day', pp.full_reg_datetime, now()) BETWEEN 7 AND 29 THEN 'B. 2-4 weeks'
            WHEN dateDiff('day', pp.full_reg_datetime, now()) BETWEEN 30 AND 59 THEN 'C. 2 months'
            WHEN dateDiff('day', pp.full_reg_datetime, now()) BETWEEN 60 AND 89 THEN 'D. 3 months'
            WHEN dateDiff('day', pp.full_reg_datetime, now()) BETWEEN 90 AND 119 THEN 'E. 4 months'
            WHEN dateDiff('day', pp.full_reg_datetime, now()) BETWEEN 120 AND 149 THEN 'F. 5 months'
            WHEN dateDiff('day', pp.full_reg_datetime, now()) BETWEEN 120 AND 179 THEN 'G. 6 months'
            WHEN dateDiff('day', pp.full_reg_datetime, now()) BETWEEN 180 AND 364 THEN 'H. 7-12 months'
            WHEN dateDiff('day', pp.full_reg_datetime, now()) BETWEEN 365 AND 729 THEN 'I. 1-2 years'
            ELSE 'J. > 2 years'
        END AS weeks_group,
        -- For checking if usage is 1 or more
        IF(ua.open_actions >= 1, 1, 0) AS open_actions_1_or_more,
        IF(ua.open_parent_activity >= 1, 1, 0) AS open_parent_activity_1_or_more,
        IF(ua.open_func_records >= 1, 1, 0) AS open_func_records_1_or_more,
        IF(ua.listen_live_clicks >= 1, 1, 0) AS listen_live_clicks_1_or_more,
        IF(ua.listen_live_starts >= 1, 1, 0) AS listen_live_starts_1_or_more,
        IF(ua.open_func_noise >= 1, 1, 0) AS open_func_noise_1_or_more,
        IF(ua.noise_sent_clicks >= 1, 1, 0) AS noise_sent_clicks_1_or_more,
        IF(ua.noise_requests >= 1, 1, 0) AS noise_requests_1_or_more,
        IF(ua.open_func_zones >= 1, 1, 0) AS open_func_zones_1_or_more,
        IF(ua.zones_place_creates >= 1, 1, 0) AS zones_place_creates_1_or_more,
        IF(ua.zones_place_creates_done >= 1, 1, 0) AS zones_place_creates_done_1_or_more,
        IF(ua.open_func_appstat >= 1, 1, 0) AS open_func_appstat_1_or_more,
        IF(oac.open_settings >= 1, 1, 0) AS open_settings_1_or_more,
        IF(oac.open_family >= 1, 1, 0) AS open_family_1_or_more,
        IF(oac.open_chat >= 1, 1, 0) AS open_chat_1_or_more,
        -- For checking if usage is 2 or more
        IF(ua.open_actions >= 2, 1, 0) AS open_actions_2_or_more,
        IF(ua.open_parent_activity >= 2, 1, 0) AS open_parent_activity_2_or_more,
        IF(ua.open_func_records >= 2, 1, 0) AS open_func_records_2_or_more,
        IF(ua.listen_live_clicks >= 2, 1, 0) AS listen_live_clicks_2_or_more,
        IF(ua.listen_live_starts >= 2, 1, 0) AS listen_live_starts_2_or_more,
        IF(ua.open_func_noise >= 2, 1, 0) AS open_func_noise_2_or_more,
        IF(ua.noise_sent_clicks >= 2, 1, 0) AS noise_sent_clicks_2_or_more,
        IF(ua.noise_requests >= 2, 1, 0) AS noise_requests_2_or_more,
        IF(ua.open_func_zones >= 2, 1, 0) AS open_func_zones_2_or_more,
        IF(ua.zones_place_creates >= 2, 1, 0) AS zones_place_creates_2_or_more,
        IF(ua.zones_place_creates_done >= 2, 1, 0) AS zones_place_creates_done_2_or_more,
        IF(ua.open_func_appstat >= 2, 1, 0) AS open_func_appstat_2_or_more,
        IF(oac.open_settings >= 2, 1, 0) AS open_settings_2_or_more,
        IF(oac.open_family >= 2, 1, 0) AS open_family_2_or_more,
        IF(oac.open_chat >= 2, 1, 0) AS open_chat_2_or_more,
        rp.flg_premium,
        rp.uid != '' as flg_subscription
    FROM
        analytics.parent_properties AS pp
    LEFT JOIN
        user_activity AS ua ON ua.uid = pp.uid
    LEFT JOIN
        open_action_counts AS oac ON oac.uid = pp.uid
    LEFT JOIN recent_payments as rp on rp.uid = pp.uid
    WHERE
        pp.is_full_register = 1
        AND pp.full_reg_datetime >= '2025-01-01'
        AND pp.full_reg_datetime < '2025-01-28'
        AND pp.region in ('ru', 'global')
        AND pp.platform in ('Android', 'iOS')
)
SELECT *
FROM metrics
ORDER BY user_uid
LIMIT 1000000;