with
visitParamExtractString(addJson, 'id') AS event_id_,

exp_users as
  (select uid,
          if(MAX(visitParamExtractString(addJson, 'option') = 'new'), 'New', 'Old') as exp_group
   from stat.funnelTrack
   where dt >= '2024-08-16'
     and `action` = 'pmr_overlay_push'
     and region in ('ru', 'global')
     and appVersion >= 2007731
   group by 1),

groups as
  (select exp_group,
          uniqExact(uid) as all
   from exp_users eu
   group by 1),

pushes_sent_new as
  (select es.exp_group,
          count(user_uid) as users
   from analytics.pushes_sent ps
   join exp_users es on ps.user_uid = es.uid
   where sub_type = 'PF'
     and dt >= '2024-08-16'
     and es.exp_group = 'New'
   group by 1),

pushes_sent_old as
  (select es.exp_group as exp_group,
          count(uid) as users
   from stat.funnelTrack ft
   join exp_users es on ft.uid = es.uid
   where action = 'custom_push'
     and visitParamExtractRaw(addJson, 'type') = '1224'
     and dt >= '2024-08-16'
     and es.exp_group = 'Old'
     group by 1),

pushes_sent_old_info as
  (select event_id_ as event_id,
          uid
   from stat.funnelTrack
   join exp_users using(uid)
   where action = 'custom_push'
     and visitParamExtractRaw(addJson, 'type') = '1224'
     and dt >= '2024-08-16'
     and exp_group = 'Old'),

pushes_open_old AS (
    SELECT
        eg.exp_group as exp_group,
        count(ft.uid) as uid
    FROM
        stat.funnelTrack ft
    JOIN
        exp_users eg ON ft.uid = eg.uid
    JOIN
        pushes_sent_old_info pi ON pi.uid = ft.uid AND pi.event_id = visitParamExtractString(addJson, 'event')
    WHERE
        action = 'push_open'
        AND dt >= '2024-08-16'
        and exp_group = 'Old'
        GROUP BY exp_group
),

pushes_open_new as
  (select exp_group,
          count(if(action='overlay_btn_target', uid, null)) as overlay_btn_target
   from stat.funnelTrack
   join exp_users using(uid)
   where dt >= '2024-08-16'
     and action = 'overlay_btn_target'
     and region in ('ru', 'global')
     and exp_group = 'New'
   group by 1)

select gs.exp_group as exp_group,
       gs.all as users,
       ps.users+su.users as sent,
       sent/all as sent_per_user,
       uae.uid+opo.overlay_btn_target as open,
       open/sent as CTR
from groups gs
left join pushes_sent_new ps on gs.exp_group = ps.exp_group
left join pushes_sent_old su on gs.exp_group = su.exp_group
left join pushes_open_old uae on gs.exp_group = uae.exp_group
left join pushes_open_new opo on gs.exp_group = opo.exp_group