Untitled

kirzecy670

Jun 16th, 2025 (edited)

194

Never

Add comment

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

MySQL 1.60 KB | None | 0 0

raw download clone embed print report

WITH
-- Users who had 'open' event in last 60 days
users_with_open AS (
SELECT DISTINCT uid
FROM stat.funnelTrack
WHERE action = 'open'
AND region = 'ru'
AND FMKIsAndroidParent(deviceType, appVersion)
AND dt >= today()
),
-- Users who had 'open_first' event in last 60 days
users_with_open_first AS (
SELECT DISTINCT uid
FROM stat.funnelTrack
WHERE action = 'open_first'
AND dt >= today() - 60
),
-- Users who had open but not open_first
target_users AS (
SELECT uid
FROM users_with_open
WHERE uid NOT IN (SELECT uid FROM users_with_open_first)
),
-- Reasons from test data (your original query)
test_rejection_reasons AS (
SELECT
uid,
simpleJSONExtractString(addJson, 'details') AS rejection_reason,
dt AS rejection_date
FROM stat.funnelTrack
WHERE action IN ('feature_disabled', 'feature_deactivated')
AND dt >= today() - 60
AND (
simpleJSONExtractString(addJson, 'experiment') = 'android_gmd_57483_newApp_v01_ru_experiment'
OR simpleJSONExtractString(addJson, 'experimentId') = 'android_gmd_57483_newApp_v01_ru_experiment'
)
-- AND simpleJSONExtractString(addJson, 'details') = 'first install does not match'
)
-- Final analysis
SELECT
COUNT(DISTINCT t.uid) AS total_target_users,
COUNT(DISTINCT r.uid) AS users_with_rejection_data,
r.rejection_reason,
COUNT(DISTINCT r.uid) AS users_per_reason
FROM target_users t
LEFT JOIN test_rejection_reasons r ON t.uid = r.uid
GROUP BY r.rejection_reason
ORDER BY users_per_reason DESC

Add Comment

Please, Sign In to add comment