Проверяла тестовые задания начинающих аналитиков. Где ошибаются почти все

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

К середине стопки я угадывала, где человек споткнётся, ещё до того, как дочитаю его SQL. Таких мест набралось пять.

Условный магазин

Данные выдуманные, а болячки у них настоящие, из рабочих выгрузок. Условный интернет-магазин, август прошлого года, три таблицы. В orders заказы: номер, покупатель, время оформления, сумма и статус. В order_items состав заказа: какой товар и сколько штук. В users покупатели и их город.

На решение нужен вечер, от силы два. Ловушек я заложила четыре: дубли, пустые значения, время в UTC и один очень крупный заказ. Про часовой пояс написано прямо в описании столбца.

Лишние деньги после JOIN

Эту ошибку я не закладывала, её кандидаты устраивают себе сами. Чтобы посчитать товары в заказе, нужна order_items. Её присоединяют к заказам и к покупателям, группируют по городу и складывают amount. Синтаксис безупречный, а результат врёт.

В заказе на 1500 рублей три разных товара, значит, после JOIN у него три строки. Каждая несёт с собой те же 1500, и SUM складывает 4500. Заказ один, а COUNT(*) насчитает три. Выручка по городам в таких решениях выходила в два с лишним раза больше настоящей, и авторов это не смущало.

Правильно сначала свернуть состав заказа до строки на заказ, а уже потом присоединять. Фильтры по периоду и статусу тут опускаю.

WITH items AS (
  SELECT order_id, SUM(qty) AS qty
  FROM order_items
  GROUP BY order_id
)
SELECT u.city,
       COUNT(*) AS orders,
       SUM(o.amount) AS revenue,
       AVG(i.qty) AS avg_qty
FROM orders o
JOIN items i ON i.order_id = o.order_id
JOIN users u ON u.user_id = o.user_id
GROUP BY u.city;

Но мне интереснее привычка сверяться. Сумма amount по заказам за тот же период, без единого JOIN, должна совпасть с суммой по городам. В бухгалтерии обороты по субсчетам обязаны сойтись с оборотом по счёту, и я до сих пор не умею это не проверять. Вышло больше — строки размножились. Меньше — кто-то потерялся по дороге, например покупатель, которого нет в users.

Рядом лежит первая настоящая ловушка: два заказа выгрузились дважды, строка в строку. Заметить их можно одной проверкой — сравнить COUNT(*) и COUNT(DISTINCT order_id). Кто-то дубли нашёл и молча выкинул, кто-то не заметил. Для меня это почти одно и то же: в обоих случаях я не знаю, что человек увидел в данных. Нашёл странное — напиши об этом одной фразой, даже если уже исправил.

Где прячется NULL

У части заказов пустой статус. Отменённые заказы в выручку брать не надо, и фильтр пишут невинный на вид:

WHERE status <> 'cancelled'

Заказы с пустым статусом из такого запроса исчезают. Сравнение NULL с чем угодно даёт «неизвестно», а WHERE оставляет только строки, где условие истинно. Ни ошибки, ни предупреждения, просто выручка меньше. Если пустые статусы нужны, условие пишут иначе: status IS DISTINCT FROM 'cancelled' или явно добавляют OR status IS NULL.

Брать их в выручку или нет, в задании нарочно не сказано. Мне всё равно, что выберет кандидат, лишь бы записал: «Заказы без статуса считаю оплаченными, их столько-то на такую-то сумму».

Тот же NULL сидит в городе. GROUP BY собирает покупателей без города в одну строку с пустым названием, её принимают за мусор и удаляют вместе с парой сотен тысяч выручки. Я бы подписала её через COALESCE(u.city, 'не указан') и оставила.

Август по Гринвичу

Время заказа в выгрузке записано в UTC. Москва живёт на три часа впереди, стрелки у нас не переводят с 2014 года. Значит, август по Москве начинается в 21:00 31 июля по UTC и заканчивается в 21:00 31 августа. А самый частый фильтр в решениях такой:

WHERE created_at BETWEEN '2025-08-01' AND '2025-08-31'

В нём две ошибки. Первая — тот самый сдвиг: первые три часа августа по Москве в UTC ещё июль, и они пропадают. Вторая: дата без времени означает полночь, так что '2025-08-31' — это начало последнего дня. Почти всё 31 августа в выборку не попадает.

WHERE created_at >= '2025-08-01 00:00+03'
  AND created_at <  '2025-09-01 00:00+03'

Так пишут для столбца timestamptz. В столбце без пояса PostgreSQL смещение в строке молча проигнорирует, и границы придётся задать сразу в UTC: от '2025-07-31 21:00' до '2025-08-31 21:00'. Левая граница включена, правая нет, и ни один день не теряется.

Один кандидат построил график заказов по часам и написал, что пик приходится на девять утра: люди заказывают по дороге на работу. Только девять по UTC — это полдень по Москве. Пик был обеденный.

Московское время из timestamptz даёт created_at AT TIME ZONE 'Europe/Moscow'. В столбце без пояса базе сначала сообщают, что там UTC: created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Moscow'. Похоже на опечатку, но так и надо.

Четырнадцать дней против семнадцати

С бесплатной доставкой ошибаются по-крупному. Типичный ответ: до 15 августа выручка 4,2 миллиона, после — 5,61, рост на треть, доставка сработала. Арифметика верная. Только в первом периоде 14 дней, а во втором 17.

Сравнивать надо выручку в день. А потом находится заказ от 28 августа: одна компания заказала товаров на 240 000 рублей, видимо, обставляла офис. Один такой заказ весит больше, чем сотня обычных. Средний чек перед такими выбросами беззащитен, поэтому рядом с ним я всегда хочу видеть медиану.

Без этого заказа картина простая: заказов в день стало больше на процент, чек вырос на четыре. Перемножаю: 1,012 × 1,041 ≈ 1,053, те самые пять процентов. Раз растёт чек, я бы посмотрела на заказы чуть дороже порога, от 2000 до 2300 рублей: если люди докладывают в корзину до бесплатной доставки, это видно там.

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

Пять предложений для владельца

Третий вопрос кажется формальностью, его решают в последний вечер за десять минут. А я открываю каждое решение с него. SQL владельцу магазина никто не покажет, ему покажут эти пять предложений.

Чаще всего вывод пересказывает запрос: «были посчитаны заказы по городам, затем выручка была сгруппирована». Или звучит уверенно и без единой оговорки: «бесплатная доставка увеличила выручку на 33,6 %, рекомендуем сохранить». Второе хуже, потому что по нему примут решение.

На моих условных цифрах вывод, который я хотела бы получить, выглядит примерно так:

Во второй половине августа магазин зарабатывал в день примерно на 5 % больше, чем в первой, если не считать одного корпоративного заказа на 240 000 рублей. Связывать рост только с бесплатной доставкой рано: на конец августа пришлись школьные покупки и больше выходных. Заказов чуть дороже 2000 рублей стало больше, это похоже на действие порога, но для уверенности нужно сравнение с прошлым годом.

Подзапросы вместо CTE, решение в таблице вместо SQL, кривые отступы — это я прощаю легко. Не прощаю числа, про которое автор не может сказать, откуда оно взялось.

Первые десять минут с любым решением у меня уходят на одно и то же. Сверяю сумму по городам с суммой по всей таблице за тот же период. Ищу в тексте слова «дубль», «пусто», «UTC», и если нет ни одного, SQL читаю особенно внимательно. Смотрю, как задан период: BETWEEN с голыми датами почти всегда значит, что последний день потерян. И ищу в выводе рубли и хотя бы одну оговорку.

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