Условие

Junior-задание для аналитика в дистрибьюторской компании. Включает несколько мини-задач:

SQL: вывести топ-10 клиентов по выручке за последний месяц.
SQL: посчитать среднюю выручку на клиента в разрезе регион × продуктовая категория.
Python/Excel: построить ABC-анализ по клиентам.
Бизнес-кейс: ваш регион показывает рост продаж 10% YoY, но команда жалуется на падение маржинальности. Что вы предложите для расследования?

Решение

1. Top-10 клиентов

SELECT client_id, SUM(amount) AS revenue
FROM sales
WHERE sale_date >= NOW() - INTERVAL '1 month'
GROUP BY client_id
ORDER BY revenue DESC
LIMIT 10;

2. Region × Category

SELECT c.region, p.category,
       COUNT(DISTINCT s.client_id)            AS n_clients,
       SUM(s.amount)                          AS total_rev,
       SUM(s.amount) * 1.0
         / COUNT(DISTINCT s.client_id)        AS avg_rev_per_client
FROM sales s
JOIN clients  c ON c.id = s.client_id
JOIN products p ON p.id = s.product_id
WHERE s.sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY c.region, p.category
ORDER BY c.region, total_rev DESC;

3. ABC-анализ клиентов

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

import pandas as pd
 
df = pd.read_csv('sales.csv')
rev = df.groupby('client_id')['amount'].sum().sort_values(ascending=False)
rev = rev.reset_index()
rev['cum_share'] = rev['amount'].cumsum() / rev['amount'].sum()
 
def abc(x):
    if x <= 0.80: return 'A'
    if x <= 0.95: return 'B'
    return 'C'
 
rev['abc_class'] = rev['cum_share'].apply(abc)
print(rev.groupby('abc_class').size())

4. Бизнес-кейс: рост Revenue 10% YoY, падение маржинальности

Декомпозиция:

\text{Profit} = \text{Revenue} - \text{COGS} - \text{Discount}

\text{Margin\%} = (\text{Revenue} - \text{COGS}) / \text{Revenue}

Возможные причины:

Mix shift: продают больше дешёвых SKU (низкий margin %), меньше премиум.
- Проверка: tariff/category mix YoY.
Рост скидок: конкурентная среда → больше промо.
- Проверка: discount_rate = (gross - net) / gross.
Cost push: COGS вырос (логистика, импорт).
- Проверка: COGS per unit YoY.
Channel mix: больше продают через дистрибьюторов с худшим markup.
Customer mix: новые крупные клиенты с большими скидками.
One-off events: акции, sale.

Action plan:

Декомпозиция: revenue × margin% per product / region / channel.
Сравнение margin% YoY по сегментам — где сильнее всего упал.
Pricing review: цены и discount по top-SKU.
Cost analysis: что подорожало.
Recommendation: фокус на продаже high-margin SKU, пересмотр discount-политики, hedging логистики.

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

«За последний месяц» — calendar month или rolling 30 days? Уточняйте.
Margin% vs absolute Profit: рост revenue с падением margin% может всё равно увеличить absolute profit. Сначала проверьте оба.
ABC по клиентам vs по SKU: разные применения. Уточните.
Возвраты: revenue минус returns или нет?
VAT: сумма с НДС или без — корректность маржи требует net.

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

(1) GROUP BY client + SUM + ORDER + LIMIT. (2) JOIN + GROUP BY с COUNT(DISTINCT). (3) cumulative share + classification. (4) Декомпозиция revenue × margin% / mix / discount / cost → найти главный driver падения margin.

ТАБАКОН — задачи Junior Data Analyst: SQL, Python, бизнес-кейс