Условие

Стандартный набор Junior-задач для банка:

SQL: из transactions(client_id, txn_date, amount, mcc_category) найти топ-10 категорий MCC по сумме транзакций за последний квартал.
SQL: для каждого клиента посчитать среднюю сумму транзакции и медиану.
Excel/Python: построить ABC-XYZ-анализ клиентов (по сумме transactions × частоте).
Кейс: банк хочет повысить retention. Какие сегменты клиентов выделили бы для таргетной коммуникации?

Решение

1. Топ-10 MCC категорий

SELECT mcc_category,
       SUM(amount)                AS total_amount,
       COUNT(*)                    AS n_txns,
       COUNT(DISTINCT client_id)   AS n_clients
FROM transactions
WHERE txn_date >= NOW() - INTERVAL '3 month'
GROUP BY mcc_category
ORDER BY total_amount DESC
LIMIT 10;

2. Средняя и медиана на клиента

SELECT client_id,
       AVG(amount)                                            AS avg_txn,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)    AS median_txn,
       COUNT(*)                                                AS n_txns
FROM transactions
GROUP BY client_id
ORDER BY n_txns DESC;

3. ABC-XYZ-анализ

ABC — по объёму (cum share):

A: top-80% выручки (≈ top-20% клиентов).
B: следующие 15% выручки.
C: 5%.

XYZ — по стабильности (CV — coefficient of variation):

X: CV < 10% (стабильные).
Y: 10–25% (умеренно стабильные).
Z: > 25% (нестабильные).

import pandas as pd
df = pd.read_csv('clients.csv')
 
# Monthly volumes per client
monthly = df.groupby([df['client_id'], df['txn_date'].dt.to_period('M')])['amount'].sum().reset_index()
 
stat = (monthly.groupby('client_id')['amount']
                .agg(total='sum', mean='mean', std='std')
                .reset_index())
stat['cv'] = stat['std'] / stat['mean']
 
# ABC
stat = stat.sort_values('total', ascending=False)
stat['cum_share'] = stat['total'].cumsum() / stat['total'].sum()
stat['ABC'] = stat['cum_share'].apply(lambda x: 'A' if x <= 0.8 else 'B' if x <= 0.95 else 'C')
stat['XYZ'] = pd.cut(stat['cv'], [0, 0.1, 0.25, 100], labels=['X', 'Y', 'Z'])
 
print(stat.groupby(['ABC', 'XYZ']).size().unstack())

4. Сегменты для retention

Принцип: не все клиенты одинаково «дорогие» — начинать с тех, у кого высокий потенциал и риск ухода.

Подходящие сегменты:

AX (топ-выручка, стабильные) — VIP, защищать любой ценой через персональный менеджер.
AY (топ-выручка, нестабильные) — точно следить за behavior signals (сильное падение — alert).
BZ (средние, нестабильные) — потенциальные churners; SMS/push-кампании, скидки.
CZ (мало транзакций, нестабильные) — низкая ценность, малый ROI на удержание; напоминание о существовании.
Sleeping (нет транзакций ≥ 3 мес) — пробуждение через push.
Активные новые (зарегистрированы < 3 мес, ≥ 5 транзакций) — onboarding flow.

Дополнительные срезы:

Демография: молодые vs возрастные.
Каналы: онлайн-only vs офлайн.
Продуктовый mix: только дебет vs кредит/инвестиции.

Подводные камни

PERCENTILE_CONT работает в Postgres / Snowflake; в MySQL — придётся через NTILE или window functions.
MCC categories: тысячи различных кодов — обычно агрегируют до 20–30 главных групп.
amount отрицательное (зачисление vs списание) — для retention важна активность, для прибыли — отдельно.
txn_date в timestamp: используйте date_trunc для группировок.
ABC pareto stable для retail-банка: 20% клиентов дают 80% comissions.
CV для нестабильности: проблема с клиентами с малым числом транзакций (std неустойчив). Минимум n >= 6 месяцев.

Эталонный ответ

(1) Group by mcc + sum + order desc + limit 10. (2) Group by client + avg + percentile_cont(0.5). (3) ABC по cum share + XYZ по CV; cross-table 3×3. (4) Сегменты для retention: AX (VIP), AY/AZ (alert), BZ/CZ (campaigns), Sleeping (revival).

Ак Барс — задачи Junior Аналитика по платежам и клиентским сегментам