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

SQL на собеседовании: запрос, NULL, дубли и проверка результата

Практика SQL на интервью: обращения поддержки, первый ответ, SLA, NULL и дубли. Исполнимые данные, запрос и проверенный результат SQLite.

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

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

Ниже — оригинальный учебный кейс поддержки. Он предназначен для живого обсуждения; домашнее тестовое аналитика с другими данными — отдельный формат. Скрипт данных, решение и автопроверка приложены полностью.

Что уточнить до запроса

Попроси назвать СУБД и версию, смысл ключей, допустимые пропуски и ожидаемую гранулярность. Время, округление и работа с NULL различаются между системами. В этом упражнении используется SQLite; время хранится целым числом минут от начала одной учебной смены, поэтому переносить арифметику на реальные timestamp без адаптации нельзя.

На публичной странице Т-Банка для дата-инженеров среди тем названы JOIN, группировки, NULL и окна. Это пример программы одной роли, а не единый стандарт всех SQL-интервью.

Перед решением спроси:

  • Что считается ответом: сообщение сотрудника, бота, внутренний комментарий?
  • Считать скорость только отвеченных или всех обращений?
  • Какой момент наблюдения и истёк ли срок SLA?
  • Повторная доставка события — дубль или отдельное сообщение?
  • Время календарное или рабочее, какая временная зона?

Условие: как быстро отвечает поддержка

Есть tickets(ticket_id, channel, opened_min) и messages(event_id, message_key, ticket_id, sent_min, actor, is_public). ticket_id уникален в обращениях; event_id уникален в журнале доставки. Один message_key может повториться из-за повторной доставки того же сообщения. Для этого кейса такие повторы имеют одинаковые поля сообщения.

Нужно на минуте 240 показать по каналу: количество созданных обращений, количество отвеченных, количество без ответа, среднее время первого публичного ответа человека и долю всех обращений с ответом в SLA. SLA чата — 15 минут включительно, почты — 120. Боты и внутренние заметки не считаются; сообщения с неизвестным временем исключаются из определения первого ответа и отдельно требуют проверки качества данных.

ОбращениеКаналОткрытоПервый подходящий ответЗадержка
1chat055
2chat104030
3email0180180
4email60нетNULL
5chat10011010
6email05050

В журнале для обращения 1 есть бот на минуте 1, два экземпляра одного ответа на минуте 5 и следующее сообщение на минуте 8. У обращения 2 есть внутренняя заметка на минуте 15. У обращения 6 — ещё одно сообщение сотрудника с sent_min = NULL. Полный журнал содержится в скрипте данных.

Как написать решение

Сначала сверни журнал до одной строки на обращение, затем считай показатели по каналам. LEFT JOIN сохраняет обращения без подходящего ответа. Условия к сообщениям находятся в ON: перенос m.actor = 'agent' в WHERE удалил бы строки без совпадения. Эта семантика соединения описана в документации SQLite.

WITH first_reply AS (
  SELECT t.ticket_id, t.channel, t.opened_min,
         MIN(m.sent_min) AS first_min
  FROM tickets AS t
  LEFT JOIN messages AS m
    ON m.ticket_id = t.ticket_id
   AND m.actor = 'agent'
   AND m.is_public = 1
   AND m.sent_min >= t.opened_min
   AND m.sent_min <= 240
  WHERE t.opened_min <= 240
  GROUP BY t.ticket_id, t.channel, t.opened_min
)
SELECT channel,
       COUNT(*) AS created,
       COUNT(first_min) AS answered,
       SUM(CASE WHEN first_min IS NULL THEN 1 ELSE 0 END)
         AS unanswered,
       ROUND(AVG(first_min - opened_min), 1)
         AS avg_answered_min,
       SUM(CASE WHEN first_min - opened_min <=
                     CASE WHEN channel = 'chat' THEN 15 ELSE 120 END
                THEN 1 ELSE 0 END) AS within_sla,
       ROUND(100.0 * SUM(CASE WHEN first_min - opened_min <=
                     CASE WHEN channel = 'chat' THEN 15 ELSE 120 END
                THEN 1 ELSE 0 END) / COUNT(*), 1) AS sla_pct_all
FROM first_reply
GROUP BY channel
ORDER BY channel;

Для учебной схемы допустимы только два канала. В реальной системе нормативы лучше вынести в справочник с периодом действия; ELSE 120 для неизвестного канала молча назначил бы неправильное правило.

Какой результат должен получиться

КаналСозданоОтвеченоБез ответаСреднее отвеченных, минВ SLAДоля всех, %
chat33015,0266,7
email321115,0133,3

Проверь вручную: чат — (5 + 30 + 10) / 3 = 15 минут; почта — (180 + 50) / 2 = 115. Почтовое обращение 4 остаётся в количестве созданных, но не в среднем отвеченных. Его ожидание к минуте 240 равно 180 минутам, поэтому срок уже превышен. В этом наборе у всех обращений окно SLA завершилось.

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

Почему NULL нельзя заменить нулём

В SQLite COUNT(*) считает строки, COUNT(first_min) — известные значения, а AVG игнорирует NULL: это описано в справочнике агрегатов. Замена неизвестного времени на ноль означала бы мгновенный ответ и занизила среднее. first_min = NULL тоже не является проверкой пропуска: нужен IS NULL.

Ответ интервьюеру: «Без ответа — отдельное состояние. Я сохраню его в знаменателе доли всех обращений, но не превращу в нулевую задержку. Среднее по отвеченным может выглядеть хорошим при большой очереди, поэтому покажу рядом число ожидающих и их возраст».

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

Как работать с дублями и оконными функциями

Повтор одинакового сообщения не меняет MIN, поэтому первый ответ остаётся верным. Но COUNT(*) по исходному журналу посчитал бы доставки вместо уникальных сообщений. Для подсчёта числа ответов сначала выдели DISTINCT message_key, ticket_id, sent_min, actor, is_public при заданной гарантии одинаковых повторов. Если версии могут различаться, потребуется правило выбора актуальной версии.

Когда нужно получить автора или полный текст первого ответа, MIN(sent_min) отдельно от остальных полей недостаточно. Можно ранжировать уже очищенные сообщения по обращению через ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY sent_min, message_key) и выбрать rn = 1. Второй ключ задаёт воспроизводимый порядок при совпадении времени. Принцип оконных функций разобран в PostgreSQL; конкретный синтаксис проверь для своей СУБД.

Не применяй DISTINCT ко всему готовому результату ради сокрытия лишних строк: сначала выясни кардинальность соединений.

Как проверить запрос на интервью

  1. Получи строки промежуточного CTE и сверь шесть обращений с таблицей условия.
  2. Проверь сумму каналов: шесть созданных, пять отвеченных, одно ожидающее.
  3. Добавь повтор доставки: результат должен сохраниться.
  4. Добавь обращение без ответа: знаменатель вырастет, среднее отвеченных сохранится.
  5. Проверь границу SLA, пустой набор, неизвестный канал и событие из будущего.

Microsoft рекомендует объяснять ход решения. Если не помнишь функцию, обозначь нужную операцию и уточни диалект. MIT предлагает практику интервью: попробуй сначала объяснить расчёт без кода, затем написать его и прогнать приложенную проверку командой python3 check.py из папки файлов.

Автопроверка запускает SQLite без сторонних пакетов, сравнивает ожидаемые строки, добавляет дубль, обращение без ответа и будущее обращение. Лог выполнения приложен. Подготовка к более широкому разговору — в статье об интервью аналитика данных.

Источники