Поиск аномалий через оконные функции и z-score

Условие задания

**Контекст:** Телеком-оператор мониторит потребление трафика абонентами. Нужно автоматически выявлять аномальное потребление (возможная утечка, взлом или мошенничество).

**Данные:**

[см. код в задании]

**Задание:**

1. Для каждого пользователя рассчитайте скользящее среднее и стандартное отклонение трафика за последние 30 дней
2. Вычислите **z-score** текущего дня: $$z = \frac{x - \mu}{\sigma}$$
3. Отметьте как аномалию дни, где |z-score| > 3
4. Выведите только аномальные записи с деталями: user_id, дата, фактический трафик, среднее, стдев, z-score, тарифный план

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE daily_traffic (
    user_id INT NOT NULL,
    traffic_date DATE NOT NULL,
    data_mb NUMERIC(12,2) NOT NULL,     -- потреблённый трафик в MB
    tariff_plan VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, traffic_date)
);

Темы

z-score оконные функции аномалии STDDEV статистика

Подсказки

• AVG(data_mb) OVER (PARTITION BY user_id ORDER BY traffic_date ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) — среднее за предыдущие 30 дней (без текущего)
• STDDEV аналогично через оконную функцию с тем же фреймом
• z-score = (data_mb - avg) / NULLIF(stddev, 0)
• ABS(z_score) > 3 — стандартный порог для аномалий (правило 3 сигм)

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Поиск аномалий через оконные функции и z-score"?

Это задание для уровня Senior. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: z-score, оконные функции, аномалии, STDDEV, статистика.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 425 SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания