Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH
- reinstall AS (
- SELECT DISTINCT uid
- FROM stat.funnelTrack
- WHERE dt >= '2025-01-01'
- AND FMKIsAndroidParent(deviceType, appVersion)
- AND (
- (action IN ('bind_user', 'rebind_user')
- AND visitParamExtractString(addJson, 'reason') IN (
- 'email', 'loginByEmailAndCode', 'restoreByAdId',
- 'adid', 'moveGlobalToRu', 'migration'
- ))
- OR action = 'reinstall_detected'
- )
- ),
- circle_owners AS (
- SELECT user_id,
- argMin(permission_role, id) AS role
- FROM circles.circle_member
- GROUP BY user_id
- HAVING role = 'Owner'
- ),
- first_open_users AS (
- SELECT
- uid,
- full_reg_datetime::date AS cohort_date,
- IF(uid IN (SELECT uid FROM reinstall), 'rebind', 'not_rebind') AS rebind_group,
- IF(children_quantity >= 1, 'has_child', 'no_child') AS child_group
- FROM analytics.parent_properties_v pp
- JOIN circle_owners co ON co.user_id = pp.id
- WHERE full_reg_datetime >= '2025-05-01'
- AND platform = 'Android'
- AND region = 'ru'
- ),
- cohort_sizes AS (
- SELECT
- cohort_date,
- concat(rebind_group, ' / ', child_group) AS user_group,
- uniqExact(uid) AS cohort_size
- FROM first_open_users
- GROUP BY cohort_date, user_group
- )
- SELECT
- cohort_date,
- user_group,
- cohort_size
- FROM cohort_sizes
- ORDER BY cohort_date, user_group;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
