Untitled

kirzecy670

Jul 2nd, 2025 (edited)

220

Never

Add comment

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

MySQL 3.45 KB | None | 0 0

raw download clone embed print report

--//
WITH
-- Пользователи с ролью Owner
owner_users AS (
SELECT user_id
FROM circles.circle_member
GROUP BY user_id
HAVING argMin(permission_role, id) = 'Owner'
),
-- Пользователи с circles_concept_enabled
with_flag AS (
SELECT DISTINCT user_id::UInt64 AS user_id
FROM stat.user_setting_history
WHERE name = 'circles_concept_enabled'
AND ts >= '2025-06-16'
AND value IN (1, '1', '"1"')
),
-- Пользователи с ребиндами или reinstall
reinstall_users 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_id из событий circle_concept_migration_pingo_joined_circle
circle_concept_migration AS (
SELECT CAST(JSONExtractInt(addJson, 'circle_id') AS UInt64) AS circle_id
FROM stat.funnelTrack
WHERE dt >= '2025-06-01'
AND action = 'circle_concept_migration_pingo_joined_circle'
GROUP BY circle_id
),
-- Пользователи, попавшие в circle_concept_migration
circle_concept_migration_users AS (
SELECT DISTINCT cm.user_id AS user_id
FROM circles.circle_member cm
INNER JOIN circle_concept_migration u ON cm.circle_id = u.circle_id
),
-- Пользователи, попавшие в parent_properties_fmk
in_fmk AS (
SELECT id, full_reg_datetime::Date AS reg_date
FROM analytics.parent_properties_fmk
),
-- Базовая когорта — все пользователи
base_users AS (
SELECT
pp.id AS user_id,
pp.uid AS uid,
pp.full_reg_datetime::Date AS full_reg_date,
pp.children_quantity,
pp.platform,
pp.region
FROM analytics.parent_properties_v pp
JOIN owner_users ow ON ow.user_id = pp.id
-- JOIN circle_concept_migration_users ccmu ON ccmu.user_id = pp.id
WHERE pp.full_reg_datetime >= '2025-06-01'
AND pp.full_reg_datetime < '2025-07-01'
AND pp.platform = 'Android'
AND pp.region = 'ru'
),
-- Присоединяем фильтры и флаг FMK
filtered AS (
SELECT
b.full_reg_date,
b.user_id,
b.uid as uid,
1 AS is_base,
IF(f.user_id != 0, 1, 0) AS has_flag,
IF(r.uid != '', 1, 0) AS is_reinstall,
IF(b.children_quantity >= 1, 1, 0) AS has_children,
IF(fmk.id != 0, 1, 0) AS is_in_fmk
FROM base_users b
LEFT JOIN with_flag f ON b.user_id = f.user_id
LEFT JOIN reinstall_users r ON b.uid = r.uid
LEFT JOIN in_fmk fmk ON b.user_id = fmk.id AND b.full_reg_date = fmk.reg_date
)
-- Агрегация
SELECT
full_reg_date,
uniqExact(uid) AS base_users,
countIf(is_reinstall = 0) AS not_reinstall_users,
countIf(is_in_fmk = 1) AS fmk_id_users,
countIf(has_children = 1) AS with_children,
countIf(is_reinstall = 0 AND has_children = 1 AND is_in_fmk = 1) AS passed_filters,
countIf(has_flag = 1) AS newui_users,
countIf(is_reinstall = 0 AND has_children = 1 AND is_in_fmk = 1 AND has_flag = 1) AS passed_all_filters
FROM filtered
GROUP BY full_reg_date
ORDER BY full_reg_date DESC LIMIT 30;

Add Comment

Please, Sign In to add comment