Untitled

kirzecy670

Jul 9th, 2025

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

PostgreSQL 3.02 KB | None | 0 0

raw download clone embed print report

--// ПУШИ
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,
count(uid) as users
from stat.funnelTrack ft
join exp_users es on ft.user_uid = es.uid
where action = 'custom_push'
and visitParamExtractRaw(addJson, 'type') = '1224'
and dt >= '2024-08-16'
and exp_group = 'Old'),
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,
uniqExact(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 all,
ps.users+su.users as sent,
sent/all as sent_cr,
CASE WHEN exp_group = 'New' THEN uae.overlay ELSE 0 END as overlay_show,
overlay_show/sent as overlay_to_sent_cr,
CASE WHEN exp_group = 'New' THEN overlay_btn_target WHEN exp_group = 'Old' THEN opo.uid END as btn_target,
CASE WHEN exp_group = 'New' THEN btn_target/overlay_show WHEN exp_group = 'Old' THEN btn_target/sent END as open_to_target_cr,
CASE WHEN exp_group = 'New' THEN overlay_btn_close ELSE 0 END as btn_close,
CASE WHEN exp_group = 'New' THEN btn_close/overlay_show ELSE 0 END as open_to_close_cr
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.e_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

Add Comment

Please, Sign In to add comment