SQL для mobile app аналитики

Зачем это знать

Mobile-first компании (VK, Tinder, Duolingo, Mobile-focused Ozon) требуют mobile analytics expertise. Specific metrics: install rate, day-N retention, version adoption, in-app purchases.

На собесах в mobile-heavy companies SQL скил с app-specific queries ожидается.

Ключевые таблицы

-- installs
(user_id, installed_at, platform, app_version, source)

-- sessions
(user_id, session_id, started_at, platform, app_version)

-- events
(user_id, event_name, created_at, properties)

-- iap_transactions
(user_id, product_id, amount, purchased_at)

Installs

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 per platform

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;

D1 / D7 / D30 retention

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;

Session length

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

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;

Version adoption

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;

Видно, как новая version gains adoption.

In-app purchase conversion

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

Average Revenue per Daily Active User:

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;

Funnel: install → first purchase

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 / referral

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;

Notifications effectiveness

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)

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;

Platform comparison

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 vs Android обычно дают different patterns.

На собесе

«Mobile retention — как?»

Install → DAU на day N. Cohort by install date.

«iOS vs Android?»

Retention, ARPU, session length часто differ. Analyze separately.

«Crash analysis?»

Event «app_crash» tracking. Per version / platform / OS version.

Связанные темы

• SQL для когортного анализа
• Как считать retention
• DAU/MAU stickiness
• SQL для воронок

FAQ

AppMetrica / Firebase — SQL?

Часто data exported в warehouse. SQL на warehouse.

Cross-device?

Hard. User ID linking (если logged in) помогает.

Attribution mobile?

MMP (AppsFlyer, Adjust) обычно. SQL аналитика сверху.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.