23 апреля 2026 г.

SQL для gaming аналитики

Карьерник — квиз-тренажёр в Telegram с 1500+ вопросами для собесов аналитика. SQL, Python, A/B, метрики. Бесплатно.

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

Gaming industry — тяжёлая аналитика. Playrix, Wargaming, Mail.ru Games, Zenit, многие Indian / Chinese games. High monetization, sophisticated analytics.

На собесах в gaming companies — unique questions.

Specific metrics

DAU / MAU

Standard, но scale huge.

Retention D1 / D7 / D30

Critical. Benchmark:

  • D1: 30-40%
  • D7: 10-20%
  • D30: 3-5%

Casual games lower. RPG higher.

ARPDAU

Average Revenue Per Daily Active User.

Low in F2P:

  • Games: $0.05-$0.50
  • Strong games: $1-$3

ARPPU

Average Revenue Per Paying User.

  • Casual: $10-50/month
  • Hardcore: $50-200
  • Whales: $1000+

Paying user %

  • Casual: 2-5%
  • Hardcore: 5-15%

Very imbalanced. 1% whales = 30% revenue typically.

Session metrics

  • Sessions per day
  • Session length
  • Sessions per player

Revenue

Monetization

  • IAP (in-app purchase)
  • Ads (rewarded, interstitial)
  • Subscription
  • Premium (one-time purchase)

Different strategies.

Revenue per install

SELECT
    install_source,
    COUNT(*) AS installs,
    SUM(revenue_30d) / COUNT(*) AS revenue_per_install
FROM cohort_30d
WHERE installed_at >= CURRENT_DATE - 60
GROUP BY install_source;

LTV projection

Actual 30-90 day LTV → project к 365 day:

SELECT
    install_month,
    SUM(CASE WHEN days_since_install <= 30 THEN revenue END) AS rev_d30,
    SUM(CASE WHEN days_since_install <= 90 THEN revenue END) AS rev_d90,
    -- Project: d90 / d30 × expected_ratio_for_365
    SUM(CASE WHEN days_since_install <= 30 THEN revenue END) * 2.5 AS projected_ltv_365
FROM cohort_revenue
GROUP BY install_month;

Player segments

Whale / Dolphin / Minnow

  • Whales: top 0.5-1% by spend ($1000+)
  • Dolphins: next 5-10%
  • Minnows: rest paying
  • Non-payers: biggest
WITH player_spend AS (
    SELECT user_id, SUM(purchase_amount) AS total_spent
    FROM purchases
    WHERE purchased_at >= CURRENT_DATE - 30
    GROUP BY user_id
)
SELECT
    CASE
        WHEN total_spent >= 1000 THEN 'Whale'
        WHEN total_spent >= 100 THEN 'Dolphin'
        WHEN total_spent > 0 THEN 'Minnow'
        ELSE 'Non-payer'
    END AS segment,
    COUNT(*) AS players,
    SUM(total_spent) AS total_revenue
FROM player_spend
GROUP BY 1;

Game-specific analyses

Progression

Which levels / stages stuck players?

SELECT
    level_id,
    COUNT(*) AS attempts,
    SUM(CASE WHEN completed THEN 1 ELSE 0 END) AS completions,
    AVG(CASE WHEN completed THEN 1.0 ELSE 0 END) AS completion_rate,
    AVG(attempts_count) AS avg_attempts
FROM level_attempts
GROUP BY level_id
ORDER BY completion_rate;

Low CR — too hard.

Economy balance

Virtual currency earned vs spent:

SELECT
    DATE(event_at) AS day,
    SUM(CASE WHEN event_type = 'earn' THEN amount END) AS earned,
    SUM(CASE WHEN event_type = 'spend' THEN amount END) AS spent,
    AVG(ending_balance) AS avg_balance
FROM currency_events
GROUP BY day
ORDER BY day;

Inflation / deflation detect.

Item / skin usage

SELECT
    item_id,
    COUNT(DISTINCT user_id) AS unique_users,
    COUNT(*) AS uses
FROM item_usage
WHERE used_at >= CURRENT_DATE - 30
GROUP BY item_id
ORDER BY uses DESC;

Which items popular, which dead.

Social features

SELECT
    COUNT(DISTINCT sender_id) AS senders,
    COUNT(DISTINCT recipient_id) AS recipients,
    COUNT(*) AS total_messages
FROM in_game_messages
WHERE sent_at >= CURRENT_DATE - 7;

Community health.

Funnel

Install → activation → retention → monetization.

WITH funnel AS (
    SELECT
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'install') AS installs,
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'tutorial_completed') AS tutorial,
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'level_1') AS level_1,
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'first_purchase') AS first_purchase
    FROM events
    WHERE event_at >= CURRENT_DATE - 30
)
SELECT * FROM funnel;

A/B in games

Live-ops (promotions)

Test offers, discounts, packs.

Quick turnaround.

Monetization

Pricing, currency amounts.

Balance

Level difficulty.

UX

UI changes, tutorials.

Every change test.

Churn prediction

Features:

  • Days since last session
  • Session frequency trend
  • Spend trend
  • Progress stuck?
  • Friends quit?

Model: XGBoost обычно.

Intervention: push, email, bonus.

Tools

Platforms

  • GameAnalytics — free
  • Unity Analytics
  • Firebase
  • Amplitude (custom)
  • Custom ETL

BI

Internal dashboards. Real-time important.

Scale

Games с millions DAU → ClickHouse, Snowflake.

Gaming companies

Russian

  • Playrix (world-class mobile)
  • Mail.ru Games
  • Wargaming (Belarus / global)
  • Gaijin
  • Nival

International big

  • Blizzard
  • Riot
  • Epic
  • Supercell

Similar analytics, different scale / products.

Specific challenges

Vast scale

Billions events daily. ClickHouse, Druid.

Real-time

Live-ops need immediate data.

Cross-platform

iOS, Android, PC — unified view.

User ID

Multiple IDs per user. Identification challenge.

Fraud

Cheaters, payment fraud.

Собес в gaming

«LTV mobile game как?»

Cohort-based, projected from early data.

«Whale percentage?»

0.5-1% typical. Heavy revenue concentration.

«Retention benchmarks?»

D1 30-40%, D7 10-20%, D30 3-5%.

«Monetization techniques?»

Freemium, IAP, ads, season pass, subscriptions.

«A/B in live ops?»

Daily experiments. Offers, content, balance.

Зарплаты

  • Junior: 100-150k ₽
  • Middle: 180-280k ₽
  • Senior: 280-400k ₽

Playrix / top companies — upper range.

Plus bonuses tied к game performance.

Learning

Reading

  • GameAnalytics blog
  • Mobile dev blogs
  • GDC talks

Tools

Unity Analytics, Firebase — free.

Kaggle

Some game datasets.

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

FAQ

Gaming background нужен?

No. Adapt from other industries.

Real-time обязателен?

Live-ops — yes. Отчётность — batch ok.

ML часто?

Churn, LTV projection, recommendation — yes.

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

Тренироваться в Telegram