Выведите всех операторов поддержки с числом закрытых обращений, включая тех, у кого закрытых нет

ЗадачаMiddleЧасто

Проверила команда СопроводОбновлено

Короткий ответ

Возьмите операторов левой таблицей и присоедините обращения через LEFT JOIN, а фильтр status = 'closed' поставьте в ON. Тогда оператор без закрытых обращений останется в выборке со строкой из NULL справа. Считайте count(t.id): он пропускает NULL и дает 0. count(*) посчитал бы эту пустую строку и вернул 1.

Почему этот вопрос задают

Это самая частая ловушка с внешним соединением: запрос выглядит верно, а операторы без закрытых обращений молча пропадают из отчета. Задача показывает, понимаете ли вы, когда срабатывает условие и что считает COUNT.

Условие задачи

Служба поддержки интернет-провайдера ведет таблицы agents(id, name) и tickets(id, agent_id, status). В agents пять операторов: 1 Алина, 2 Борис, 3 Вадим, 4 Галина, 5 Денис. Обращение закрыто, если status = 'closed'. Выведите каждого оператора и число его закрытых обращений по алфавиту имен. Операторы без закрытых обращений тоже попадают в отчет с нулем: у Вадима обращений нет совсем, у Галины только открытые.

idagent_idstatus
1011closed
1021closed
1031open
1041closed
1052open
1062closed
1074open
1084open
1095closed
1105closed

Как подойти к решению

  1. Возьмите операторов левой таблицей, чтобы в отчет попал каждый из них
  2. Поставьте условие на статус в ON: так оно отбирает обращения до соединения
  3. Считайте count(t.id), чтобы у оператора без пары получился 0
  4. Сверьте результат с условием: пять строк, у Вадима и Галины нули

Решение

Условие в ON отбирает пары до соединения, и операторы без закрытых обращений получают строку с NULL справа. В WHERE то же условие отбросило бы эти строки после соединения, и Вадим с Галиной пропали бы. count(t.id) не считает NULL, поэтому у них 0. Отчет по всем операторам читает обращения целиком, индекс по tickets(agent_id) пригодится, когда в отчете два-три оператора.

SQL
SELECT a.name, count(t.id) AS closed_tickets
FROM agents a
LEFT JOIN tickets t
  ON t.agent_id = a.id
 AND t.status = 'closed'
GROUP BY a.id, a.name
ORDER BY a.name;
Вывод
  name  | closed_tickets
--------+----------------
 Алина  |              3
 Борис  |              1
 Вадим  |              0
 Галина |              0
 Денис  |              2
(5 rows)

Что спросят следом

  • Как одним запросом посчитать у каждого оператора и закрытые, и открытые обращения?

  • Как вывести только операторов, у которых нет ни одного закрытого обращения?

  • Что будет с обращением, у которого agent_id пустой?

Доступно по подписке

Ответы на эти вопросы

Частые ошибки

  • «Отфильтрую закрытые обращения в WHERE после соединения»

    Фильтр в WHERE отбросит строки, где справа NULL, и LEFT JOIN сработает как внутреннее соединение. Условие на правую таблицу ставьте в ON

  • «Посчитаю count(*), это то же самое»

    Вадим и Галина получат по 1 за пустую строку справа. Считайте столбец, который в найденной паре всегда заполнен: t.id или t.agent_id из условия ON. Столбец с пропусками занизит счет

  • «Добавлю в WHERE условие OR t.status IS NULL, и все починится»

    Галина все равно пропадет: у ее обращений статус есть, он просто другой. Такое условие спасает только операторов совсем без обращений

  • «На всякий случай оберну счетчик в coalesce(count(t.id), 0)»

    count никогда не возвращает NULL, обертка лишняя. coalesce нужен для sum и avg: по группе без значений они дают NULL

Сигналы для интервьюера

Сильный ответ

Уточняет, за какой период нужен отчет, и показывает, что фильтр по дате обращения тоже уходит в ON

Тревожный сигнал

Группирует только по имени и не думает, что два оператора с одинаковым именем сольются в одну строку

Красный флаг

Меняет LEFT JOIN на INNER JOIN и решает, что операторы без закрытых обращений отчету не нужны

Как еще могут спросить

  • Почему LEFT JOIN превращается в INNER JOIN, если добавить условие в WHERE?
  • Как посчитать записи из второй таблицы так, чтобы строки без пары остались с нулем?
  • Выведите всех сотрудников с количеством выполненных задач, включая тех, у кого их нет
  • Почему count(*) после LEFT JOIN дает 1 там, где пары нет?

Похожие вопросы

ЗнаниеJuniorЧасто

Какие виды JOIN бывают и чем LEFT JOIN отличается от INNER JOIN?

Основные виды: INNER, LEFT, RIGHT, FULL и CROSS JOIN. INNER JOIN оставляет только строки, у которых нашлась пара по условию ON. LEFT JOIN сохраняет все строки левой таблицы, а там, где пары нет, заполняет столбцы правой значением NULL. Поэтому LEFT JOIN берут, когда важны и записи без совпадений, например читатели без выдач.

  • JOIN
261
ЗадачаMiddleЧасто

Найдите клиентов, которые ни разу не сделали заказ

Проверьте для каждого клиента, что у него нет ни одного заказа: WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). Тот же ответ даст LEFT JOIN с условием o.customer_id IS NULL. Вариант NOT IN (SELECT customer_id FROM orders) опасен: достаточно одного заказа с пустым customer_id, и запрос вернет ноль строк.

  • NOT EXISTS
  • JOIN
248
ЗадачаMiddleЧасто

Посчитайте накопительную выручку по дням месяца, включая дни без заказов

Соберите календарь дней через generate_series и присоедините к нему заказы через LEFT JOIN, чтобы пустые дни не пропали. Сверните заказы до одной строки на день через GROUP BY и coalesce(sum(o.amount), 0.00): без coalesce у пустого дня вместо нуля будет NULL. Поверх дневной выручки посчитайте sum(revenue) OVER (ORDER BY day): окно сложит все дни от начала периода до текущего.

  • оконные функции
  • generate_series
210
ЗадачаSeniorРедко

Выведите все подкатегории раздела каталога на любой глубине вложенности с помощью рекурсивного запроса

Напишите WITH RECURSIVE из двух частей. Якорь выбирает сам раздел, рекурсивный шаг присоединяет к найденным строкам их детей по parent_id, пока новых строк не останется. По дороге копите уровень и путь в массиве, в конце сортируйте по этому массиву. Добавьте массив пройденных id: проверка по нему остановит рекурсию, если в данных есть цикл.

  • WITH RECURSIVE
  • иерархия
82

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

Короткие ответы на частые вопросы по этой теме.

Скажите, какая таблица главная и что будет с оператором без пары: после соединения справа у него NULL. Из этого сразу следуют оба решения, условие в ON и count(t.id). Так интервьюер слышит, что вы выводите запрос из механики соединения.

Разберите на бумаге двух крайних операторов: без обращений совсем и только с открытыми. Пройдите шаги: что даст соединение, что останется после фильтра, что посчитает агрегат. Эти два случая ломают большинство неверных решений, поэтому интервьюеры кладут их в данные.

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

Общие вопросы на интервью

Общий вопросС опытомЧасто

Почему вы хотите работать у нас?

Назовите две-три причины, которые связывают вас именно с этой компанией: продукт, задачи, масштаб, команда. Каждую подкрепите фактом, который нашли сами: новость, проект, текст вакансии, свой опыт клиента. Потом покажите, что принесете со своей стороны. Слова о стабильности и известном бренде подходят любой компании, поэтому на них ответ не держится.

  • мотивация
  • о компании
290
Общий вопросБез опытаЧасто

Какие у вас сильные стороны?

Хватит двух-трех сильных сторон, которые нужны на этой работе, если к каждой есть пример: что сделали и какой был результат. Берите качества из текста вакансии, а не из списка «ответственный, коммуникабельный». Без опыта примеры ищите в учебе, практике и подработке. Говорите спокойно, без лишней скромности и без хвастовства.

  • сильные стороны
  • без опыта
276
Общий вопросБез опытаЧасто

Кем вы видите себя через 5 лет?

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

  • мотивация
  • карьерные планы
289
ИсторияС опытомЧасто

Расскажите о конфликте с коллегой: из-за чего он возник и как вы его решили?

Расскажите один реальный конфликт по рабочему вопросу: из-за чего разошлись, чего хотела каждая сторона и как вы договорились. Говорите спокойно, без оценок коллеги. Покажите, что вы искали решение, где-то уступили и сохранили рабочие отношения. Закончите результатом и тем, что теперь делаете, чтобы похожий спор не повторился.

  • конфликты
  • работа в команде
252
ИсторияС опытомЧасто

Каким профессиональным достижением вы гордитесь больше всего?

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

  • достижения
  • результат в цифрах
276
Общий вопросБез опытаЧасто

Опыта работы еще нет, а интервьюер просит рассказать о себе: что вы скажете?

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

  • о себе
  • без опыта
244

Вопросы руководителю на интервью

ИсторияРуководительЧасто

Как вы делегируете задачи: что отдаете команде, а что оставляете себе?

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

  • делегирование
  • команда
255
КейсРуководительЧасто

Два сотрудника вашей команды месяц не разговаривают друг с другом, и общий отчет уже сорвал срок — что сделаете?

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

  • конфликт
  • ответственность
242
ИсторияРуководительЧасто

Расскажите о сотруднике, которого вы вырастили: каким он пришел и чего добился?

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

  • развитие сотрудников
  • наставничество
303
ИсторияРуководительЧасто

Расскажите о цели, которую ваша команда не выполнила: когда вы это поняли и что сделали?

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

  • цели
  • провал
241
Общий вопросРуководительЧасто

Как вы измеряете результат своей команды?

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

  • показатели
  • цели
216
ИсторияРуководительЧасто

Расскажите, как вы разложили цель компании на задачи для своей команды

Возьмите одну цель компании с цифрой и сроком и покажите, как вы ее разбили: на какие части, по какому признаку, какие личные цели получил каждый сотрудник. Расскажите, как следили за ходом и что меняли по дороге. Закончите итогом план-факт честно, даже если цель выполнена не целиком.

  • цели
  • планирование
227