SQL для аналитики мобильных приложений
SELECT * FROM orders, users без WHERE и без JOIN ... ON. В orders 1000 строк, в users 500 строк. Что вернёт запрос?Зачем это знать
Mobile-first компании (VK, Ozon, крупные игровые студии, EdTech вроде Duolingo и Skyeng) ждут от аналитика владения метриками именно мобильных продуктов: install rate, day-N retention, доля пользователей на новой версии, конверсия в первую покупку. Это не «SQL вообще», а SQL поверх специфичной для приложений схемы событий.
На собесе в такие компании почти всегда дают задачу на app-специфичный запрос — посчитать retention по когорте установок, сравнить iOS и Android, разобрать crash rate по версиям. Ниже — типовые таблицы и готовые запросы под эти вопросы.
Ключевые таблицы
В мобильной аналитике почти всё крутится вокруг четырёх сущностей: установки, сессии, события внутри приложения и покупки. Схема упрощённая, но по сути такая же в любом продукте:
-- инсталлы: одна строка на установку
(user_id, installed_at, platform, app_version, source)
-- сессии: заходы в приложение
(user_id, session_id, started_at, platform, app_version)
-- события: любые действия внутри приложения
(user_id, event_name, created_at, properties)
-- покупки внутри приложения
(iap_transactions: user_id, product_id, amount, purchased_at)Инсталлы
Базовый срез — сколько установок пришло по дням, платформам и источникам. С него начинается почти любой отчёт по привлечению:
SELECT
DATE(installed_at) AS day,
platform,
source,
COUNT(*) AS installs
FROM installs
WHERE installed_at >= CURRENT_DATE - 30
GROUP BY 1, 2, 3
ORDER BY 1 DESC;DAU по платформам
DAU (Daily Active Users) считается по уникальным пользователям с сессией за день. Разрез по платформе нужен, потому что динамика iOS и Android почти всегда расходится:
SELECT
DATE(started_at) AS day,
platform,
COUNT(DISTINCT user_id) AS dau
FROM sessions
WHERE started_at >= CURRENT_DATE - 30
GROUP BY 1, 2;Retention D1 / D7 / D30
Ключевая метрика удержания: доля пользователей из когорты установки, которые вернулись на день N. Когорта строится по дате установки, активность берётся из сессий. Обратите внимание на LEFT JOIN — он сохраняет всю когорту, даже тех, кто больше не возвращался:
WITH cohort AS (
SELECT user_id, DATE(installed_at) AS install_date
FROM installs
WHERE installed_at >= CURRENT_DATE - 60
),
activity AS (
SELECT DISTINCT user_id, DATE(started_at) AS active_date
FROM sessions
)
SELECT
c.install_date,
COUNT(DISTINCT c.user_id) AS installs,
COUNT(DISTINCT CASE WHEN a.active_date = c.install_date + 1 THEN c.user_id END) * 100.0 /
COUNT(DISTINCT c.user_id) AS d1_retention,
COUNT(DISTINCT CASE WHEN a.active_date = c.install_date + 7 THEN c.user_id END) * 100.0 /
COUNT(DISTINCT c.user_id) AS d7_retention,
COUNT(DISTINCT CASE WHEN a.active_date = c.install_date + 30 THEN c.user_id END) * 100.0 /
COUNT(DISTINCT c.user_id) AS d30_retention
FROM cohort c
LEFT JOIN activity a ON a.user_id = c.user_id
AND a.active_date BETWEEN c.install_date AND c.install_date + 30
GROUP BY c.install_date
ORDER BY c.install_date;Тонкость, о которой любят спрашивать: здесь считается classic retention (вернулся именно на day N). Есть ещё rolling retention (вернулся на day N или позже) — уточните у интервьюера, какой из них нужен.
Длительность сессии
Длину сессии считают как разницу между первым и последним событием внутри неё. Медиана здесь честнее среднего, потому что распределение сильно скошено длинными «фоновыми» сессиями:
WITH session_durations AS (
SELECT
user_id,
session_id,
MAX(created_at) - MIN(created_at) AS duration
FROM events
GROUP BY user_id, session_id
)
SELECT
AVG(EXTRACT(EPOCH FROM duration) / 60) AS avg_minutes,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration) AS median_duration
FROM session_durations;Crash rate
Падения ловят через событие app_crash и нормируют на число активных пользователей, чтобы получить долю затронутых. Разрез по версии и платформе позволяет быстро понять, что зарелизили сломанную сборку:
SELECT
DATE(created_at) AS day,
platform,
app_version,
COUNT(*) AS crashes,
COUNT(DISTINCT user_id) AS affected_users,
COUNT(DISTINCT user_id) * 100.0 /
(SELECT COUNT(DISTINCT user_id) FROM sessions
WHERE DATE(started_at) = DATE(created_at)) AS crash_rate_pct
FROM events
WHERE event_name = 'app_crash'
GROUP BY 1, 2, 3;Распространение версий
Показывает, как новая версия набирает долю пользователей после релиза. Оконная функция считает процент внутри каждого дня, так что видно скорость выката:
SELECT
DATE(started_at) AS day,
app_version,
COUNT(DISTINCT user_id) AS users,
COUNT(DISTINCT user_id) * 100.0 / SUM(COUNT(DISTINCT user_id)) OVER (PARTITION BY DATE(started_at)) AS pct
FROM sessions
WHERE started_at >= CURRENT_DATE - 14
GROUP BY 1, 2
ORDER BY 1 DESC, users DESC;Так видно, насколько быстро новая версия набирает долю — если через неделю после релиза на ней меньше половины аудитории, значит выкат тормозит.
Конверсия в покупку
Считаем, какая доля когорты установки совершила хотя бы одну покупку за первые 30 дней. Ограничение purchased_at <= install_date + 30 фиксирует окно, чтобы старые когорты не выглядели искусственно лучше свежих:
WITH cohort AS (
SELECT user_id, DATE(installed_at) AS install_date
FROM installs
WHERE installed_at BETWEEN '2026-03-01' AND '2026-03-31'
)
SELECT
COUNT(DISTINCT c.user_id) AS cohort_size,
COUNT(DISTINCT iap.user_id) AS paying_users,
COUNT(DISTINCT iap.user_id) * 100.0 / COUNT(DISTINCT c.user_id) AS paid_conversion_rate
FROM cohort c
LEFT JOIN iap_transactions iap ON iap.user_id = c.user_id
AND iap.purchased_at <= c.install_date + 30;ARPDAU
ARPDAU (Average Revenue per Daily Active User) — средняя выручка на одного активного пользователя в день. Считается как дневная выручка, делённая на DAU того же дня:
WITH daily AS (
SELECT
DATE(started_at) AS day,
COUNT(DISTINCT user_id) AS dau
FROM sessions
GROUP BY 1
),
revenue AS (
SELECT
DATE(purchased_at) AS day,
SUM(amount) AS daily_revenue
FROM iap_transactions
GROUP BY 1
)
SELECT
d.day,
d.dau,
COALESCE(r.daily_revenue, 0) AS revenue,
COALESCE(r.daily_revenue, 0) / d.dau AS arpdau
FROM daily d
LEFT JOIN revenue r USING (day)
ORDER BY d.day;Воронка: инсталл → первая покупка
Воронка показывает, где отваливаются пользователи по пути от установки до первой оплаты: открыли приложение, прошли туториал, купили. Часто её собирают на витрине user_summary, где на каждого пользователя уже посчитаны ключевые тайминги:
SELECT
DATE_TRUNC('month', installed_at) AS cohort,
COUNT(*) AS installs,
COUNT(CASE WHEN first_session_at IS NOT NULL THEN 1 END) AS opened,
COUNT(CASE WHEN tutorial_completed_at IS NOT NULL THEN 1 END) AS completed_tutorial,
COUNT(CASE WHEN first_purchase_at IS NOT NULL THEN 1 END) AS purchased,
COUNT(CASE WHEN first_purchase_at IS NOT NULL THEN 1 END) * 100.0 / COUNT(*) AS install_to_paid
FROM user_summary
GROUP BY 1
ORDER BY 1;Deep-link и реферальные источники
Сравнение источников установки не только по объёму, но и по качеству: удержание на 7-й день и конверсия в оплату к 30-му. Дешёвый источник с мёртвым retention обычно хуже дорогого с живым:
SELECT
source,
COUNT(*) AS installs,
AVG(CASE WHEN d7_retained THEN 1 ELSE 0 END) * 100 AS d7_retention,
AVG(CASE WHEN paid_d30 THEN 1 ELSE 0 END) * 100 AS paid_conversion
FROM installs
WHERE installed_at >= CURRENT_DATE - 60
GROUP BY source
ORDER BY installs DESC;Эффективность пуш-уведомлений
Пуши оценивают по двум метрикам: open rate (открыли уведомление) и session rate (после пуша открыли приложение в ближайшие минуты). Второе честнее, потому что показывает реальный возврат, а не просто тап:
WITH push_events AS (
SELECT
user_id,
sent_at,
MAX(CASE WHEN opened THEN 1 ELSE 0 END) AS opened_push,
MAX(CASE WHEN event_name = 'session_start'
AND created_at BETWEEN sent_at AND sent_at + INTERVAL '10 min'
THEN 1 ELSE 0 END) AS session_after_push
FROM push_notifications
GROUP BY user_id, sent_at
)
SELECT
AVG(opened_push) * 100 AS open_rate,
AVG(session_after_push) * 100 AS session_rate
FROM push_events;Stickiness (DAU/MAU)
Stickiness — отношение среднего DAU к MAU за тот же период. Грубо: сколько дней в месяце пользователь в среднем заходит. 0,2 означает примерно 6 дней из 30, что для многих продуктов нормально:
WITH monthly AS (
SELECT COUNT(DISTINCT user_id) AS mau
FROM sessions
WHERE started_at BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE
),
daily AS (
SELECT AVG(daily_count) AS avg_dau
FROM (
SELECT DATE(started_at) AS day, COUNT(DISTINCT user_id) AS daily_count
FROM sessions
WHERE started_at BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE
GROUP BY 1
) d
)
SELECT d.avg_dau / m.mau AS stickiness FROM daily d, monthly m;Сравнение платформ
Сводка по платформам: пользователи, сессий на человека, средний чек, удержание. iOS и Android почти всегда дают разные паттерны — обычно на iOS выше ARPU, но и стоимость привлечения дороже:
SELECT
platform,
COUNT(DISTINCT user_id) AS users,
AVG(session_count) AS sessions_per_user,
AVG(purchase_amount) AS avg_spend,
AVG(CASE WHEN retained_7d THEN 1 ELSE 0 END) AS retention_d7
FROM user_summary
GROUP BY platform;Поэтому iOS и Android почти всегда анализируют раздельно, а не усредняют в один показатель.
Как это спрашивают на собесе
«Как посчитать retention в мобильном приложении?» Стройте когорту по дате установки, активность берите из сессий, считайте долю вернувшихся на day N через COUNT(DISTINCT CASE WHEN active_date = install_date + N ...). Сразу уточните, нужен classic retention (ровно на day N) или rolling (на day N или позже) — это разные запросы, и интервьюер часто ждёт этот вопрос от вас.
«Чем отличается аналитика iOS и Android?» Retention, ARPU и длина сессии обычно расходятся между платформами, поэтому их считают раздельно и не усредняют. Готовьтесь объяснить, почему усреднение по платформам маскирует проблемы конкретной из них.
«Как разобрать всплеск падений?» Событие app_crash, разрез по app_version, platform и версии ОС, нормировка на активных пользователей. Цель — локализовать проблему до конкретной сборки: если crash rate подскочил только на новой версии, значит зарелизили баг.
Связанные темы
FAQ
Как писать SQL, если данные в AppMetrica или Firebase?
Напрямую в этих системах полноценного SQL нет, но данные обычно экспортируют в хранилище (BigQuery, ClickHouse, Postgres), и вся аналитика пишется уже там. Firebase, например, штатно выгружается в BigQuery, а AppMetrica — через Logs API. То есть SQL-навык нужен для warehouse, а не для самой SDK.
Как считать метрики при cross-device использовании?
Это сложная задача: один человек с телефона и планшета выглядит как два user_id. Частичное решение — связывать устройства по авторизованному аккаунту (единый логин даёт общий user_id). Полностью детерминированно склеить анонимных пользователей нельзя, поэтому в отчётах об этом оговариваются как об ограничении.
Как устроена атрибуция установок в мобильных приложениях?
Атрибуцией (какой источник привёл установку) обычно занимается MMP — AppsFlyer, Adjust, myTracker. Они матчат клик по рекламе с установкой и присваивают источник. SQL-аналитика строится уже поверх этих данных: MMP отдаёт source/campaign, а вы считаете retention и выручку в разрезе источников.
Почему для длины сессии берут медиану, а не среднее?
Распределение длины сессий сильно скошено: есть короткие заходы и есть аномально длинные «фоновые» сессии, которые тянут среднее вверх. Медиана устойчива к таким выбросам и точнее описывает типичного пользователя. На собесе полезно упомянуть перцентили (p50, p90) как более информативную замену среднему.
Чем classic retention отличается от rolling?
Classic retention считает пользователя удержанным, только если он вернулся ровно на day N. Rolling — если он был активен на day N или в любой день после. Rolling всегда выше и обычно используется для продуктов с редким, но регулярным использованием. Перед расчётом всегда уточняйте, какой из них ждут.
Тренируйте SQL и продуктовую аналитику — откройте тренажёр с 1500+ вопросами для собесов.