WITH
reinstall_users AS (
    SELECT DISTINCT uid
    FROM stat.funnelTrack
    WHERE dt BETWEEN '2024-01-01' AND '2026-01-01'
      AND (
          (
              action IN ('bind_user', 'rebind_user')
              AND visitParamExtractString(addJson, 'reason')
                  IN ('email','loginByEmailAndCode','restoreByAdId','adid','moveGlobalToRu','migration')
          )
          OR action = 'reinstall_detected'
      )
),
recent_users AS (
    SELECT
        uid,
        id,
        last_activity_date,
        __ts_install
    FROM
    (
        SELECT
            uid,
            MAX(dt) AS last_activity_date
        FROM
            analytics.parent_actions pa
        WHERE
            dt >= today() - INTERVAL '30 day'
            AND dt < today()
        GROUP BY uid
    ) AS p_actions
    INNER JOIN
    (
        SELECT
            uid,
            id,
            toTimeZone(min(install_datetime), 'GMT') AS __ts_install
        FROM analytics.parent_properties
        WHERE is_full_register
            AND full_reg_datetime >= today() - INTERVAL '30' DAY
            AND full_reg_datetime < today()
            [[AND {{region}}]]
            [[AND {{platform}}]]
            [[AND {{country}}]]
            AND region IN ('ru', 'global')
            AND platform IN ('iOS', 'Android')
        GROUP BY uid, id
    ) AS p_props
    USING(uid)
),
second_parents AS (
    SELECT
        usedBy AS sp_id,
        usedAt AS ts_connect
    FROM wimc.mp_secondParentConnection
    WHERE sp_id != 0
),
child_activity AS (
    SELECT
        id                    AS child,
        max(__dt_last_active) AS dt_last_active
    FROM analytics.uid_to_id AS utoi
    INNER JOIN (
        SELECT
            uid,
            max(dt) AS __dt_last_active
        FROM analytics.child_actions
        WHERE geo_session
        GROUP BY uid
    ) AS geo
    USING (uid)
    GROUP BY child
),
parent_child_links AS (
    WITH
        arraySort(
            x -> x.1,
            groupArray(
                tuple(
                    toTimeZone(ts_updated, 'GMT'),
                    if(
                        update_reason IN (
                            'user.deleteChildByRelationId',
                            'user.deleteChild',
                            'user._deletePersonalData',
                            'user._mergeChilds force child remove after cross-auth'
                        ),
                        'delete',
                        'add'
                    )
                )
            )
        ) AS updates,
        updates.2 AS updates_filtered
    SELECT
        parent,
        child,
        min(ts_updated)                                                                      AS ts_link,
        countIf(updates_filtered[-1] != 'delete') OVER (PARTITION BY child ORDER BY ts_link) AS __n_parents,
        countIf(updates_filtered[-1] != 'delete') OVER (PARTITION BY child)                  AS __total_parents
    FROM logs.parent_child_history
    WHERE parent != 0
        AND child IS NOT NULL
        AND update_reason IN (
            'user.addParentByCode',
            'child merging in user._mergeChilds',
            'watch.connectWatch',
            'watch.connect',
            'user._copyChilds',
            'user.registerNewParentChild',
            'user.getAccountByChildInvite',
            /* deletions */
            'user.deleteChildByRelationId',
            'user.deleteChild',
            'user._deletePersonalData',
            'user._mergeChilds force child remove after cross-auth'
        )
    GROUP BY parent, child
),
parents_categorized AS (
    SELECT
        ru.id                              AS parent,
        any(__ts_install)                  AS ts_install,
        argMin(__total_parents, l.ts_link) AS total_parents,
        argMin(__n_parents, l.ts_link)     AS n_parents
    FROM child_activity AS a
        INNER JOIN parent_child_links AS l ON toUInt64(l.child)  = toUInt64(a.child)
        INNER JOIN recent_users       AS ru ON toUInt64(ru.id) = toUInt64(l.parent)
    WHERE age('day', ts_link::Date, dt_last_active) >= 1 - {{ geo_lookback_window }}
        AND ts_link >= __ts_install
    GROUP BY parent
),
user_metrics AS (
    SELECT
        pp.region,
        pp.platform,
        pp.full_reg_datetime,
        pp.uid as uid,
        itt.tier, itt.country, ri.uid = '' as flg_reinstall, pp.full_reg_datetime >= today() - interval 30 day as flg_new_user,

        multiIf(
            pc.n_parents > 1,  'second+ parent',
            pc.n_parents = 1 AND pc.total_parents = 1, 'single parent',
            'first parent'
        ) AS parent_category
    FROM
        analytics.parent_properties pp
    JOIN
        recent_users ru ON toUInt64(pp.id) = toUInt64(ru.id)
    INNER JOIN
        parents_categorized pc ON toUInt64(pp.id) = toUInt64(pc.parent)
    LEFT JOIN analytics.ipc_to_tier AS itt
      ON lower(itt.ipc) = pp.ip_country
  LEFT JOIN reinstall_users ri ON pp.uid = ri.uid
    WHERE
        pp.is_full_register
        AND pp.full_reg_datetime != '1970-01-01 00:00:00'
        AND pp.full_reg_datetime < today()
        [[AND {{region}}]]
        [[AND {{platform}}]]
        [[AND {{country}}]]
        AND pp.region IN ('ru', 'global')
        AND pp.platform IN ('iOS', 'Android')
)

SELECT
    platform,
    parent_category,
    flg_reinstall,
    flg_new_user,
    tier,
    country,
    uniqCombined(uid) as Users,
    -- uniqCombined(uid) FILTER (WHERE parent_category = 'second+ parent') as Second_Parents,
    SUM(uniqCombined(uid)) OVER (PARTITION BY country) as Cohort,
    Users / Cohort AS Category_Share_of_Cohort
FROM
    user_metrics
GROUP BY
    platform,
    parent_category,
    flg_reinstall,
    flg_new_user,
    tier,
    country
ORDER BY Category_Share_of_Cohort DESC