Untitled

WITH users AS (
    SELECT
        uid,
        COUNT(*) AS days, -- Total number of active days
        SUM(LENGTH(open) + LENGTH(open_first)) AS sessions, -- Total sessions across open types
        SUM(notEmpty(open)) AS open_actions, -- Total count of 'open' actions
        SUM(notEmpty(open_function_records)) AS open_func_records, -- Total count of 'open_function_records'
    --  SUM(notEmpty(listen_live_clicked)) AS listen_live_clicks, -- Optional: Total 'listen_live_clicked' actions
    --  SUM(notEmpty(listen_live_started)) AS listen_live_starts, -- Optional: Total 'listen_live_started' actions
        SUM(notEmpty(open_function_noise)) AS open_func_noise, -- Total count of 'open_function_noise'
    --  SUM(notEmpty(noise_sent)) AS noise_sent_clicks, -- Optional: Total 'noise_sent' actions
    --  SUM(notEmpty(noise_requested)) AS noise_requests, -- Optional: Total 'noise_requested' actions
        SUM(notEmpty(open_function_zones)) AS open_func_zones, -- Total count of 'open_function_zones'
    --  SUM(notEmpty(zones_place_create)) AS zones_place_creates, -- Optional: Total 'zones_place_create' actions
    --  SUM(notEmpty(zones_place_create_done)) AS zones_place_creates_done, -- Optional: Total 'zones_place_create_done' actions
        SUM(notEmpty(open_parent_activity)) AS open_parent_activity, -- Total count of 'open_parent_activity'
        SUM(notEmpty(open_function_appstat)) AS open_func_appstat -- Total count of 'open_function_appstat'
    FROM
        analytics.parent_actions pa
    WHERE
        dt >= date_trunc('week', today()) - INTERVAL '30 day'
        AND dt < date_trunc('week', today())
    GROUP BY
        uid
),
metrics AS (
    SELECT
        region,
        platform,
        full_reg_datetime,
        uid,
        days,
        sessions,
        CASE
            WHEN dateDiff('day', full_reg_datetime, now()) < 7 THEN 'A. 1 week'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 7 AND 29 THEN 'B. 2-4 weeks'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 30 AND 59 THEN 'C. 2 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 60 AND 89 THEN 'D. 3 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 90 AND 119 THEN 'E. 4 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 120 AND 149 THEN 'F. 5 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 120 AND 179 THEN 'G. 6 months'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 180 AND 364 THEN 'H. 7-12 months'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 365 AND 729 THEN 'I. 1-2 years'
            ELSE 'J. > 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 >= '2016-01-01'
        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;

--// Гистограммы для ресерчей
WITH users AS (
    SELECT
        uid,
        COUNT(*) AS days,
        SUM(LENGTH(open) + LENGTH(open_first)) AS sessions
    FROM
        analytics.parent_actions
    WHERE
        dt >= date_trunc('week', today()) - INTERVAL '30 day'
        AND dt < date_trunc('week', today())
    GROUP BY
        uid
),
metrics AS (
    SELECT
        region,
        platform,
        full_reg_datetime,
        uid,
        days,
        sessions,
        ROUND(sessions / days) as avg_sessions,
        dateDiff('week', full_reg_datetime, now()) AS weeks_difference,
        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
    FROM
        analytics.parent_properties
    JOIN
        users USING (uid)
    WHERE
        is_full_register
        AND full_reg_datetime >= '2016-01-01'
        AND full_reg_datetime < date_trunc('week', today())
)
SELECT
    region,
    platform,
    child_group,
    days,
    IF(weeks_difference >= 156, 157, weeks_difference) AS weeks_difference,
    avg_sessions AS avg_sessions_p_day,
    uniqExact(uid) AS users
FROM
    metrics
WHERE
    region IN ('ru', 'global')
    AND platform IN ('iOS', 'Android')
GROUP BY
    region, platform, child_group, days, weeks_difference, avg_sessions_p_day
ORDER BY
    region, platform, child_group, weeks_difference, days, avg_sessions_p_day;

--//
SELECT
    uid,
    COUNT(*) AS days,
    SUM(LENGTH(open) + LENGTH(open_first)) AS sessions
FROM
    analytics.parent_actions pa
    WHERE
        dt >= date_trunc('week', today()) - INTERVAL '30 day'
        AND dt < date_trunc('week', today())
GROUP BY
    uid
LIMIT 100;

--// Страны_Тиры
SELECT dt, ipc, os
FROM billing.a_full_register_traction;

--// L1
Сегменты
- По регионам
- По Кол-ву детей
- По вторым родителям
- Дети Андроид/iOS дети, Both.
- Сколько Активных Детей
- Возраст детей?
- ???
- Сегменты по Фичам (Использовал/Не_Использовал), Статистика + Фичи на главном экране (Лета, Места, Звук и т.д.)
    - Сегменты по использованию
- Сегменты по использованию приложения (Кака-то активность в апке, напр. Кол-во дней использования? Кол-во входов? ХЗ)

--// L2
-- Посмотреть разделение по устройствам и платформам, а также гео. (сделано)*
-- Посмотреть разделение по траффику: органика и неорганика. (еще не сделано)*
-- Сделать разбивку по Подписке (еще не сделано)*
-- Сделать разбивку по Фичам (еще не сделано)*

--// L3
-- Разбил пользователей на группы, по кол-ву дней. Может надо было брать минимум 30 дней назад окно за месяц?
-- Фильтр по дате реги нужен, динамический (есть в Табло уже) // Посмотрел но там +/- голяк. Нужно было как-то брать окна с даты реги или что-то такое ХЗ, тк фильтры я делало под WAU а не когорты.
-- Надо как-то глянуть на активных с кучей дней активности, как у них сессии внутри распределены, FLAT по дням или есть какие-то пики например в начале, а потом 1-2 просмотра карты в день условно.
-- Убрать ПЕРЕУСТАНОВЩИКОВ

--// Гистограммы V2 с фичами
WITH
pre_aggregated_data AS (
    SELECT
        uid,
        dt,
        LENGTH(open) + LENGTH(open_first) AS session_length,
        notEmpty(open_function_records) AS is_open_func_records,
        LENGTH(open_function_records) AS len_func_records,
        notEmpty(open_function_noise) AS is_open_func_noise,
        LENGTH(open_function_noise) AS len_func_noise,
        notEmpty(open_function_zones) AS is_open_func_zones,
        LENGTH(open_function_zones) AS len_func_zones,
        notEmpty(open_parent_activity) AS is_open_parent_activity,
        LENGTH(open_parent_activity) AS len_parent_activity,
        notEmpty(open_function_appstat) AS is_open_func_appstat,
        LENGTH(open_function_appstat) AS len_func_appstat
    FROM
        analytics.parent_actions pa
    WHERE
        dt >= date_trunc('week', today()) - INTERVAL '30 day'
        AND dt < date_trunc('week', today())
),
users AS (
SELECT
    uid,
    COUNT(uid) AS days, -- Aggregate days
    SUM(session_length) AS sessions,
    SUM(is_open_func_records) AS open_func_records,
    SUM(len_func_records) AS len_func_records,
    SUM(is_open_func_noise) AS open_func_noise,
    SUM(len_func_noise) AS len_func_noise,
    SUM(is_open_func_zones) AS open_func_zones,
    SUM(len_func_zones) AS len_func_zones,
    SUM(is_open_parent_activity) AS open_parent_activity,
    SUM(len_parent_activity) AS len_parent_activity,
    SUM(is_open_func_appstat) AS open_func_appstat,
    SUM(len_func_appstat) AS len_func_appstat
FROM
    pre_aggregated_data
GROUP BY
    uid
ORDER BY
    sessions DESC
),
metrics AS (
    SELECT
        region,
        platform,
        toDate(full_reg_datetime) as full_reg_datetime,
        uid,
        days,
        sessions,
        open_func_records,
        len_func_records,
        open_func_noise,
        len_func_noise,
        open_func_zones,
        len_func_zones,
        open_parent_activity,
        len_parent_activity,
        open_func_appstat,
        len_func_appstat,
        CASE
            WHEN dateDiff('day', full_reg_datetime, now()) < 7 THEN 'A. 1 week'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 7 AND 29 THEN 'B. 2-4 weeks'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 30 AND 59 THEN 'C. 2 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 60 AND 89 THEN 'D. 3 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 90 AND 119 THEN 'E. 4 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 120 AND 149 THEN 'F. 5 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 120 AND 179 THEN 'G. 6 months'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 180 AND 364 THEN 'H. 7-12 months'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 365 AND 729 THEN 'I. 1-2 years'
            ELSE 'J. > 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 >= '2016-01-01'
        AND full_reg_datetime < date_trunc('week', today())
)
SELECT * FROM metrics
WHERE
    region IN ('ru', 'global')
    AND platform IN ('iOS', 'Android')
order by days DESC, sessions desc, uid;

--// Статистика
WITH users AS (
    SELECT
        uid,
        COUNT(uid) AS days,
        uniqExact(dt) FILTER (WHERE action = 'appstat') as open_func_appstat_new,
        uniqExact(dt) FILTER (WHERE action = 'open_function_appstat') as open_func_appstat,
        count(uid) FILTER (WHERE action = 'appstat') AS len_func_appstat_new,
        count(uid) FILTER (WHERE action = 'open_function_appstat') AS len_func_appstat
    FROM
        stat.funnelTrack
    WHERE
        dt BETWEEN date_trunc('week', today()) - INTERVAL '1 month' AND date_trunc('week', today())
        AND action in ('appstat', 'open_function_appstat')
    GROUP BY
        uid
)
SELECT
	sum(open_func_appstat_new) as appstat_new,
	sum(open_func_appstat) as appstat,
	round(appstat_new/appstat*100, 1) as CR,
    uniqExact(uid) FILTER (WHERE open_func_appstat_new > 0) as uniq_func_appstat_new,
	uniqExact(uid) FILTER (WHERE open_func_appstat > 0) as uniq_func_appstat,
	round(uniq_func_appstat_new/uniq_func_appstat*100, 1) as CR_
FROM
	users
limit 100;

metrics AS (
    SELECT
        region,
        platform,
        full_reg_datetime,
        uid,
        days,
        sessions,
        CASE
            WHEN dateDiff('day', full_reg_datetime, now()) < 7 THEN 'A. 1 week'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 7 AND 29 THEN 'B. 2-4 weeks'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 30 AND 59 THEN 'C. 2 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 60 AND 89 THEN 'D. 3 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 90 AND 119 THEN 'E. 4 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 120 AND 149 THEN 'F. 5 month'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 120 AND 179 THEN 'G. 6 months'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 180 AND 364 THEN 'H. 7-12 months'
            WHEN dateDiff('day', full_reg_datetime, now()) BETWEEN 365 AND 729 THEN 'I. 1-2 years'
            ELSE 'J. > 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 >= '2016-01-01'
        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;