WITH
pp as (SELECT uid from analytics.parent_properties p
    WHERE p.full_reg_datetime >= '2024-08-12'
          AND p.is_full_register
          AND child_android_cnt >= 1 AND platform = 'Android'
          AND p.uid IS NOT NULL),
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'
),
app_stat_users AS (
    SELECT
        ft.uid as uid
    FROM
        stat.funnelTrack AS ft
    WHERE
        ft.dt >= '2024-08-01'
        AND ft.action = 'open_function_appstat'
    GROUP BY
        ft.uid
),
android_parent AS (
   select uid
   from analytics.parent_properties
   where child_android_cnt >= 1
    AND full_reg_datetime >= '2024-08-01'
    AND is_full_register
),
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
    JOIN android_parent ap on ap.uid =  ft.uid
    JOIN pp on ft.uid = pp.uid

    -- LEFT ANTI JOIN app_stat_users AS asu ON ft.uid = asu.uid
    WHERE
        ft.dt >= '2024-08-01' and ft.dt <= '2024-09-01'
        AND ft.action = 'pteam_appstat_and_blocking_v2_parents'
    GROUP BY
        ft.uid
    HAVING exp_group in ('old', 'new')
)
SELECT exp_group, count(*) as n from exp_users group by 1;