WITH
exp_users AS (
    SELECT
        ft.uid AS uid,
        MAX(visitParamExtractString(ft.addJson, 'option')) AS exp_group
    FROM
        stat.funnelTrack AS ft
    WHERE
        ft.dt >= '2024-12-01'
        AND ft.action = 'pteam_appstat_v31'
    GROUP BY
        ft.uid
    HAVING
        exp_group IN ('old', 'new')
),
reinstall AS (
SELECT distinct uid
                   FROM stat.funnelTrack
                   WHERE dt between '2024-11-01' AND '2025-06-01'
                     AND (action in ('bind_user', 'rebind_user')
                              AND visitParamExtractString(addJson, 'reason') in
                                  ('email', 'loginByEmailAndCode', 'restoreByAdId',
                                   'adid', 'moveGlobalToRu', 'migration')
                       or action = 'reinstall_detected')
),
pre_aggregated_data AS (
    SELECT
        uid,
        dt,
        LENGTH(open) + LENGTH(open_first) AS session_length,
        notEmpty(open_function_records) AS is_open_func_records,
        LENGTH(open_function_records) AS len_func_records,
        notEmpty(open_function_noise) AS is_open_func_noise,
        LENGTH(open_function_noise) AS len_func_noise,
        notEmpty(open_function_zones) AS is_open_func_zones,
        LENGTH(open_function_zones) AS len_func_zones,
        notEmpty(open_parent_activity) AS is_open_parent_activity,
        LENGTH(open_parent_activity) AS len_parent_activity,
        notEmpty(open_function_appstat) AS is_open_func_appstat,
        LENGTH(open_function_appstat) AS len_func_appstat
    FROM
        analytics.parent_actions pa JOIN exp_users USING uid
    WHERE
        dt >= today() - INTERVAL '30 day'
--        AND dt < today()
        AND dt >= '2024-12-01'
),
events AS (SELECT
        uid,
        uniqExact(dt) FILTER (WHERE action = 'app_stat_button') AS is_app_stat_button,
        count(uid) FILTER (WHERE action = 'app_stat_button') AS len_app_stat_button,
        uniqExact(dt) FILTER (WHERE action = 'appstat_app_lock_toggle') AS is_app_lock_toggle,
        count(uid) FILTER (WHERE action = 'appstat_app_lock_toggle') AS len_app_lock_toggle,
        uniqExact(dt) FILTER (WHERE action = 'appstat_lock_button_click') AS is_lock_button_click,
        count(uid) FILTER (WHERE action = 'appstat_lock_button_click') AS len_lock_button_click
    FROM
        stat.funnelTrack ft JOIN exp_users USING uid
    WHERE
        ft.dt >= today() - INTERVAL '30 day'
        AND (action IN ('appstat_block_all_button_click', 'appstat_app_lock_toggle', 'appstat_lock_button_click')
        OR (action = 'app_stat_button' AND visitParamExtractString(addJson, 'from') = 'app_stat'))
--        AND dt < today()
        AND dt >= '2024-12-01'
    GROUP BY
        1
),
users AS (
SELECT
    uid,
    COUNT(uid) AS days, -- Aggregate days
    SUM(session_length) AS sessions,
    SUM(is_open_func_records) AS open_func_records,
    SUM(len_func_records) AS len_func_records,
    SUM(is_open_func_noise) AS open_func_noise,
    SUM(len_func_noise) AS len_func_noise,
    SUM(is_open_func_zones) AS open_func_zones,
    SUM(len_func_zones) AS len_func_zones,
    SUM(is_open_parent_activity) AS open_parent_activity,
    SUM(len_parent_activity) AS len_parent_activity,
    SUM(is_open_func_appstat) AS open_func_appstat,
    SUM(len_func_appstat) AS len_func_appstat
FROM
    pre_aggregated_data
GROUP BY
    uid
ORDER BY
    sessions DESC
),
metrics AS (
    SELECT
        pp.region AS region,
        pp.platform AS platform,
        pp.full_reg_datetime::date AS full_reg_datetime,
        pp.uid AS uid,
        pp.had_any_payment,
        exp_users.exp_group,
        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 150 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,
        CASE
            WHEN pp.child_android_cnt > 0 AND pp.child_ios_cnt > 0 THEN 'both'
            WHEN pp.child_android_cnt > 0 THEN 'android'
            WHEN pp.child_ios_cnt > 0 THEN 'ios'
            WHEN pp.child_watch_cnt > 0 THEN 'watch'
            ELSE 'other'
        END AS child_group,
        CASE
            WHEN users.days BETWEEN 29 AND 30 THEN 'F. 29-30 days'
            WHEN users.days BETWEEN 22 AND 28 THEN 'E. 22-28 days'
            WHEN users.days BETWEEN 15 AND 21 THEN 'D. 15-21 days'
            WHEN users.days BETWEEN 8 AND 14 THEN 'C. 8-14 days'
            WHEN users.days BETWEEN 3 AND 7 THEN 'B. 3-7 days'
            WHEN users.days BETWEEN 1 AND 2 THEN 'A. 1-2 days'
            ELSE 'Other'
        END AS activity_group,
        days,
        sessions,
        open_func_records,
        len_func_records,
        open_func_noise,
        len_func_noise,
        open_func_zones,
        len_func_zones,
        open_parent_activity,
        len_parent_activity,
        open_func_appstat,
        len_func_appstat,
        ROUND(users.sessions / NULLIF(users.days, 0), 0) AS avg_sessions_p_day,
        is_app_stat_button,
        len_app_stat_button,
        is_app_lock_toggle,
        len_app_lock_toggle,
        is_lock_button_click,
        len_lock_button_click
    FROM
        analytics.parent_properties pp
    JOIN
        exp_users ON exp_users.uid = pp.uid
    JOIN
        users ON users.uid = pp.uid
    LEFT JOIN events ON  events.uid = pp.uid
    LEFT ANTI JOIN reinstall AS ri ON pp.uid = ri.uid
    WHERE
        pp.is_full_register
        AND pp.full_reg_datetime >= '2016-01-01'
)
SELECT
    metrics.*
FROM
    metrics
HAVING region NOT IN ('none') --AND (is_app_stat_button > 0 OR len_app_stat_button > 0)
ORDER BY exp_group, region, weeks_group, full_reg_datetime, uid;