ИИ-агент
по поиску работы

Тестовое задание аналитика: данные, SQL и защита решения

Полное учебное тестовое аналитика: CSV, SQL, очистка дублей, возвраты, контроль 9750 ₽, Excel-инструкция и защита результата.

На этой странице

Хорошее решение аналитического тестового начинается с определения показателя и проверки данных. Затем идут расчёт, независимая сверка и короткий вывод для человека, который будет принимать решение. Ниже — полный оригинальный учебный кейс с CSV, SQL, ожидаемым результатом и разбором ошибок.

Скачать практикум целиком. Внутри исходные данные, запрос, запускаемый расчёт, инструкция и контрольный результат. Сначала попробуй выполнить условие самостоятельно, затем сравни решение.

Условие учебного задания

Интернет-магазин хочет сравнить каналы привлечения по оплаченным заказам сентября 2026 года с учётом возвратов, зарегистрированных к концу сентября. Нужно вывести по каждому каналу число заказов, сумму оплат, возвраты и остаток после них. Дополнительно объяснить, можно ли по этим данным перераспределять рекламный бюджет.

Все данные вымышлены. Денежная единица — рубль, суммы целые. Даты в формате YYYY-MM-DD, временные зоны не нужны: в исходнике только даты. Этот учебный показатель назовём «нетто оплат сентябрьской когорты на 30 сентября». Он не равен бухгалтерской выручке, прибыли или денежному потоку всех операций сентября.

Есть два файла:

ФайлОдна строкаПоля
orders.csvВыгруженная запись заказа; в выгрузке возможен повторorder_id, paid_at, status, channel, amount_rub
refunds.csvОтдельный возврат по заказуrefund_id, order_id, refund_at, amount_rub

В orders.csv девять строк: один точный повтор, отменённый заказ, октябрьский и августовский заказы. В refunds.csv пять операций. Один заказ может иметь несколько возвратов; один канал не заполнен.

Какие вопросы задать до расчёта

Уточни, к каким заказам относят возвраты и на какую дату смотрят. По условию берём оплаченные в сентябре заказы, а возвраты — только сентябрьские по этим заказам. Октябрьский возврат заказа 1 пока не учитываем. Сентябрьский возврат августовского заказа 8 тоже не входит в эту когорту.

Пустой канал означает неизвестный источник, а не отсутствие заказа. Отменённый заказ не является оплатой. Если paid_at в реальной системе означает дату создания, а не оплаты, нужен другой источник или уточнение. В нашем наборе для оплаченных заказов это дата оплаты по определению.

Нужен также ответ на вопрос о повторе: одинаковые ли это строки выгрузки или разные версии заказа? Здесь запись заказа 2 полностью повторена и удаляется как технический дубль. При конфликте значений одного ID расчёт должен остановиться для выяснения, а не оставить случайную строку.

Сначала проверь исходные данные

  1. Прочитай заголовки и типы, проверь уникальность идентификаторов после очистки.
  2. Удали только подтверждённый точный дубль заказа 2: остаётся восемь уникальных заказов.
  3. Проверь, что суммы положительны и возвраты связаны с известными заказами.
  4. Выдели сентябрьские оплаченные заказы: их пять.
  5. Сохрани пустой канал отдельной группой unknown.

В приложенном solve.py конфликтующий дубль вызывает ошибку. Это решение относится к заданной модели выгрузки. Для журнала изменений, где один заказ законно имеет несколько версий, понадобилось бы правило выбора актуальной записи.

Решение на SQL

Сначала сверни возвраты до одной строки на заказ. После этого соединение не размножит сумму заказа, у которого два возврата. Запрос рассчитан на SQLite; в архиве он хранится отдельным файлом solution.sql.

WITH refund_by_order AS (
  SELECT order_id, SUM(amount_rub) AS refunded
  FROM refunds
  WHERE refund_at >= '2026-09-01'
    AND refund_at < '2026-10-01'
  GROUP BY order_id
)
SELECT COALESCE(o.channel, 'unknown') AS channel,
       COUNT(*) AS orders_count,
       SUM(o.amount_rub) AS gross_rub,
       SUM(COALESCE(r.refunded, 0)) AS refunds_rub,
       SUM(o.amount_rub - COALESCE(r.refunded, 0)) AS net_rub
FROM orders o
LEFT JOIN refund_by_order r ON r.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.paid_at >= '2026-09-01'
  AND o.paid_at < '2026-10-01'
GROUP BY COALESCE(o.channel, 'unknown')
ORDER BY channel;

LEFT JOIN сохраняет заказы без возвратов. COALESCE превращает отсутствие суммы возврата в ноль, а неизвестный канал — в явно названную группу. Полуоткрытая граница периода не требует угадывать последний час месяца, если в будущем появятся временные метки.

Если переносишь решение на другой движок, проверь типы дат и синтаксис по его документации: например, в PostgreSQL есть собственные функции работы с временными значениями. Свёртка данных перед соединением полезна и как приём уменьшения объёма обработки; Google BigQuery отдельно рекомендует предварительную агрегацию. Это не основание считать производительность SQLite измеренной.

Ожидаемый результат и его проверка

КаналЗаказыОплаты, ₽Возвраты, ₽Нетто, ₽
direct120001250750
partners1400004000
search245005004000
unknown1100001000
Всего511 50017509750

Ручной контроль по заказам: 3000 − 500 = 2500; 2000 − 1000 − 250 = 750; ещё 1500, 4000 и 1000 без возвратов. Сумма — 9750 ₽. В отдельной проверке Python рассчитывает этот итог по строкам без SQL-соединения.

Запуск из распакованного каталога:

python3 solve.py

Нужен Python 3.10 или новее, сторонних зависимостей нет. Скрипт создаёт базу в памяти, выполняет SQL, сравнивает группы с ожидаемыми значениями и сохраняет result.json и result.csv.

Полезная проверка ошибки: прямое соединение заказов с несвёрнутыми возвратами повторит сумму заказа 2. Ещё одна ошибка — отфильтровать дату возврата в WHERE после LEFT JOIN: тогда заказы без возвратов могут исчезнуть. Объясни обе проблемы на маленьких исходных строках, а не только названием SQL-конструкции.

Как решить тот же кейс в Excel

Импортируй CSV через получение данных из текста, проверь разделитель, типы и даты. Удали подтверждённый точный дубль, оставив исходную выгрузку отдельно. Назови очищенную таблицу заказов Orders, таблицу возвратов Refunds.

Для каждого заказа добавь сумму возвратов по его ID и периоду. Пример для англоязычных имён функций Excel:

=SUMIFS(Refunds[amount_rub],Refunds[order_id],[@order_id],
Refunds[refund_at],">="&DATE(2026,9,1),
Refunds[refund_at],"<"&DATE(2026,10,1))

В русской локализации используются СУММЕСЛИМН, ДАТА и обычно точка с запятой между аргументами. Переносы здесь нужны для чтения: формулу вводи в одну ячейку. Для сопоставления дат столбец возвратов должен содержать даты, а не нераспознанный текст.

Добавь нетто как сумму заказа минус найденный возврат. Построй сводную по каналу с фильтрами paid и сентябрьской даты оплаты, затем сверь четыре строки с таблицей выше. Пустой канал переименуй в «Неизвестный», не удаляй его.

CSV в архиве содержит данные и ожидаемый результат, без встроенных формул. Формульный путь дан для самостоятельного воспроизведения; контрольные суммы проверены SQL и Python. Работа в нативном Excel в рамках подготовки примера не проверялась.

Как оформить вывод и защитить работу

Готовый короткий вывод: «Сентябрьская когорта содержит пять оплаченных заказов на 11 500 ₽. Сентябрьские возвраты по ним составляют 1750 ₽, нетто — 9750 ₽. Канал search даёт 4000 ₽ после возвратов по двум заказам. По этим данным нельзя сравнить окупаемость рекламы: нет расходов, маржи и достаточной выборки; у одного заказа канал неизвестен».

Для продуктовой роли следующий вопрос — почему возвращают заказы и насколько сопоставимы когорты. Для аналитика данных — полнота выгрузки, задержка возвратов и качество источника канала. Если нужен бюджетный выбор, запроси затраты и согласованную экономическую метрику; больший объём оплат сам по себе не доказывает выгодность канала.

В сдачу включи определение показателя, исходники, очистку, SQL, итог и ограничения. На защите объясни, почему сворачивал возвраты и как изменится результат при срезе на конец октября. Не подменяй ответ длинным рассказом обо всех известных функциях SQL.

Критерии самопроверки

Решение можно воспроизвести из архива; числа сходятся двумя способами; период и статусы заданы явно; неизвестный канал сохранён; вывод не обещает того, чего данных недостаточно доказать. Если изменишь источник или показатель, обнови и контроль результата.

Для подготовки к разговору о данных отдельно пригодятся SQL на собеседовании и общий маршрут интервью.

Источники

Данные и бизнес-условие созданы для обучения; это не задание действующего работодателя.