Как посчитать Crash Rate в SQL

Зачем Crash Rate

Crash Rate = % sessions / users с crash. Critical metric для mobile / desktop apps. Google Play / App Store flag high-crash apps. > 1% — investigate immediately.

Формула

Crash Rate (per session) = crashed_sessions / total_sessions × 100%
Crash-Free Users (24h) = users_without_crash / total_users × 100%

Базовый расчёт

SELECT
    DATE_TRUNC('day', session_time) AS day,
    COUNT(*) AS total_sessions,
    COUNT(*) FILTER (WHERE crashed) AS crashed_sessions,
    COUNT(*) FILTER (WHERE crashed)::NUMERIC * 100 / COUNT(*) AS crash_rate_pct
FROM app_sessions
WHERE session_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Crash-free users

WITH user_crashes AS (
    SELECT
        user_id,
        BOOL_OR(crashed) AS had_crash
    FROM app_sessions
    WHERE session_time >= CURRENT_DATE - INTERVAL '1 day'
    GROUP BY user_id
)
SELECT
    COUNT(*) AS users,
    COUNT(*) FILTER (WHERE NOT had_crash) AS crash_free_users,
    COUNT(*) FILTER (WHERE NOT had_crash)::NUMERIC * 100 / COUNT(*) AS crash_free_pct
FROM user_crashes;

Industry standard: 99.5%+ crash-free users.

По device / OS / version

SELECT
    app_version,
    device_os,
    COUNT(*) AS sessions,
    COUNT(*) FILTER (WHERE crashed) AS crashes,
    COUNT(*) FILTER (WHERE crashed)::NUMERIC * 100 / COUNT(*) AS crash_rate_pct
FROM app_sessions
WHERE session_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY app_version, device_os
HAVING COUNT(*) >= 100
ORDER BY crash_rate_pct DESC
LIMIT 30;

Specific combos (e.g., iOS 18.2 + app 4.5.1) — high crash → priority hotfix.

Top crash reasons

SELECT
    crash_type,
    crash_signature,
    COUNT(*) AS occurrences,
    COUNT(DISTINCT user_id) AS affected_users,
    MIN(crash_time) AS first_seen,
    MAX(crash_time) AS last_seen
FROM crashes
WHERE crash_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY crash_type, crash_signature
ORDER BY occurrences DESC
LIMIT 20;

Top crash signature → engineering priority.

Crash recovery

User crashed → returned?

WITH crash_users AS (
    SELECT DISTINCT user_id
    FROM crashes
    WHERE crash_time::DATE = CURRENT_DATE - 1
),
returned AS (
    SELECT DISTINCT user_id
    FROM app_sessions
    WHERE session_time::DATE = CURRENT_DATE
)
SELECT
    COUNT(c.user_id) AS crashed_yesterday,
    COUNT(r.user_id) AS returned_today,
    COUNT(r.user_id)::NUMERIC * 100 / NULLIF(COUNT(c.user_id), 0) AS return_pct
FROM crash_users c
LEFT JOIN returned r USING (user_id);

Частые ошибки

Ошибка 1. Sessions vs Users. Crash rate per session ≠ per user. Crash-free users — more meaningful.

Ошибка 2. ANR vs Crash. ANR (Application Not Responding) — different. Track separately on Android.

Ошибка 3. Stack trace dedup. Same bug, different signatures → over-counting. Cluster signatures.

Ошибка 4. App version bias. New release rolled out 10% — small sample. Wait for full rollout для real crash rate.

Ошибка 5. Background crashes. Crash в background не visible к user. Counted by SDK same way.

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

• Как посчитать app uninstall rate в SQL
• Как посчитать error rate в SQL
• Как посчитать api latency в SQL
• Как посчитать app startup time в SQL

FAQ

Какой Crash Rate ok?

< 0.5% — excellent. 0.5-1% — acceptable. > 1% — issue. > 2% — emergency.

Crash-free users vs sessions?

Users — more user-centric. Sessions — operational.

iOS / Android разница?

iOS обычно lower crash (more strict OS). Android — fragmented devices, higher.

Background crashes?

Track but лower priority — user не sees.

Crash после deploy?

Always compare new version crash rate vs baseline. Roll back если spike.