WITH
    -- Extract relevant fields from JSON
    visitParamExtractString(addJson, 'option') AS option_,
    visitParamExtractString(addJson, 'id') AS event_id_,
    visitParamExtractInt(addJson, 'type') AS push_id_,
    visitParamExtractString(addJson, 'event') AS event_name_,

-- Identify experiment groups
experiment_groups AS (
    SELECT
        uid,
        MIN(option_) AS experiment_group_id
    FROM
        stat.funnelTrack
    WHERE
        dt >= '2024-08-13'
        AND action = 'pmr_2020_highlights_experiment'
    GROUP BY
        uid
),

-- Collect sent pushes for the 'old' experiment group
sent_pushes_old AS (
    SELECT
        eg.experiment_group_id as experiment_group_id,
        ft.dt as dt,
        ft.uid as uid,
        push_id_ as push_id,
        event_id_ as event_id
    FROM
        stat.funnelTrack ft
    JOIN
        experiment_groups eg USING(uid)
    WHERE
        action IN ('cdp_push', 'admin_push', 'custom_push')
        AND dt >= '2024-08-13'
        AND push_id IN (520, 523, 524, 525, 526, 527, 530)
        AND experiment_group_id = 'old'
),

-- Collect sent pushes for the 'new' experiment group
sent_pushes_new AS (
    SELECT
        eg.experiment_group_id as experiment_group_id,
        ps.dt as dt,
        ps.user_uid AS uid,
        push_id
    FROM
        analytics.pushes_sent ps
    JOIN
        experiment_groups eg ON eg.uid = ps.user_uid
    WHERE
        dt >= '2024-08-13'
        AND sub_type = 'UP'
        AND type IN ('2hr_video', 'week_app', '2hr_games', '2hr_social', 'night_activity')
        AND eg.experiment_group_id = 'new'
),

-- Combine push IDs for matching against opened pushes
combined_push_ids AS (
    SELECT DISTINCT uid, event_id FROM (
        SELECT uid, event_id FROM sent_pushes_old
        UNION ALL
        SELECT uid, push_id AS event_id FROM sent_pushes_new
    )
),

-- Collect information about opened pushes
opened_pushes AS (
    SELECT
        eg.experiment_group_id as experiment_group_id,
        ft.uid as uid
    FROM
        stat.funnelTrack ft
    JOIN
        experiment_groups eg ON ft.uid = eg.uid
    JOIN
        combined_push_ids pi ON pi.uid = ft.uid AND pi.event_id = visitParamExtractString(addJson, 'event')
    WHERE
        action = 'push_open'
        AND dt >= '2024-08-13'
)

-- Final aggregation and reporting
SELECT
    eg.experiment_group_id as experiment_group_id,
    eg.total_users as total_users,
    -- COALESCE(sp_old.users_sent, sp_new.users_sent) AS users_sent,
    CASE
        WHEN experiment_group_id = 'old' THEN sp_old.users_sent
        WHEN experiment_group_id = 'new' THEN sp_new.users_sent
    ELSE null
    END AS users_sent,
    op.pushes_opened,
    ROUND((sp_old.pushes_sent + sp_new.pushes_sent) / eg.total_users, 2) AS pushes_sent_per_user
FROM
-- Total users per experiment group
    (SELECT experiment_group_id, uniqExact(uid) AS total_users FROM experiment_groups GROUP BY experiment_group_id) eg

-- Sent pushes for the 'old' group
LEFT JOIN
    (SELECT experiment_group_id, uniqExact(uid) AS users_sent, COUNT(*) AS pushes_sent FROM sent_pushes_old GROUP BY experiment_group_id) sp_old
ON
    sp_old.experiment_group_id = eg.experiment_group_id

-- Sent pushes for the 'new' group
LEFT JOIN
    (SELECT experiment_group_id, uniqExact(uid) AS users_sent, COUNT(*) AS pushes_sent FROM sent_pushes_new GROUP BY experiment_group_id) sp_new
ON
    sp_new.experiment_group_id = eg.experiment_group_id

-- Opened pushes for both groups
LEFT JOIN
    (SELECT experiment_group_id, uniqExact(uid) AS pushes_opened FROM opened_pushes GROUP BY experiment_group_id) op
ON
    op.experiment_group_id = eg.experiment_group_id
ORDER BY 1;