--// Сегменты
WITH users AS (
    SELECT
        uid,
        COUNT(*) AS days,
        SUM(LENGTH(open) + LENGTH(open_first)) AS sessions
    FROM
        analytics.parent_actions pa
    WHERE
        dt BETWEEN date_trunc('week', today()) - INTERVAL '1 month' AND date_trunc('week', today())
    GROUP BY
        uid
),
metrics AS (
    SELECT
        region,
        platform,
        full_reg_datetime,
        uid,
        days,
        sessions,
        CASE
            WHEN dateDiff('week', full_reg_datetime, now()::date) = 0 THEN 'A. < 1 week'
            WHEN dateDiff('week', full_reg_datetime, now()::date) = 1 THEN 'B. ~ 1 week'
            WHEN dateDiff('week', full_reg_datetime, now()::date) <= 4 THEN 'C. < 1 month'
            WHEN dateDiff('week', full_reg_datetime, now()::date) > 4 AND dateDiff('week', full_reg_datetime, now()::date) <= 8 THEN 'D. 1-2 months'
            WHEN dateDiff('week', full_reg_datetime, now()::date) > 8 AND dateDiff('week', full_reg_datetime, now()::date) <= 16 THEN 'E. 2-4 months'
            WHEN dateDiff('week', full_reg_datetime, now()::date) > 16 AND dateDiff('week', full_reg_datetime, now()::date) <= 32 THEN 'F. 4-8 months'
            WHEN dateDiff('week', full_reg_datetime, now()::date) > 32 AND dateDiff('week', full_reg_datetime, now()::date) <= 52 THEN 'G. 8-12 months'
            WHEN dateDiff('week', full_reg_datetime, now()::date) > 52 AND dateDiff('week', full_reg_datetime, now()::date) <= 104 THEN 'H. 1-2 years'
            ELSE 'I. > 2 years'
        END AS weeks_group,
        CASE
            WHEN child_android_cnt > 0 AND child_ios_cnt > 0 THEN 'both'
            WHEN child_android_cnt > 0 THEN 'android'
            WHEN child_ios_cnt > 0 THEN 'ios'
            WHEN child_watch_cnt > 0 THEN 'watch'
            ELSE 'other'
        END AS child_group,
        CASE
            WHEN days BETWEEN 29 AND 30 THEN 'f. 29-30 days'
            WHEN days BETWEEN 22 AND 28 THEN 'e. 22-28 days'
            WHEN days BETWEEN 15 AND 21 THEN 'd. 15-21 days'
            WHEN days BETWEEN 8 AND 14 THEN 'c. 8-14 days'
            WHEN days BETWEEN 3 AND 7 THEN 'b. 3-7 days'
            WHEN days BETWEEN 1 AND 2 THEN 'a. 1-2 days'
            ELSE 'other'
        END AS activity_group,
        ROUND(sessions / NULLIF(days, 0), 0) AS avg_sessions_p_day
    FROM
        analytics.parent_properties pp
    JOIN
        users USING (uid)
    WHERE
        is_full_register
        AND full_reg_datetime != '1970-01-01 00:00:00'
        AND full_reg_datetime <= date_trunc('week', today())
)
SELECT
    region,
    platform,
    activity_group,
    child_group,
    weeks_group,
    avg_sessions_p_day,
    SUM(metrics.sessions) AS session,
    SUM(days) AS day,
    uniqExact(uid) AS users
FROM
    metrics
WHERE
    region IN ('ru', 'global')
    AND platform IN ('iOS', 'Android')
GROUP BY
    1, 2, 3, 4, 5, 6
ORDER BY
    1, 2, 3, 4, 5, 6;