Finstar SQL — сотрудники с менее чем тремя задачами в работе

Условие

Две таблицы:

• employee(id, emp_name, rating) — сотрудники.
• tasks(id, assignee_id, rating) — задачи, назначенные на сотрудников.

Вывести всех сотрудников, у кого в работе менее 3 задач, в формате (имя, кол-во задач). Сотрудников без задач — тоже включить (0 задач).

Тестовые данные

employee:
1, Vasya, 5
2, Vasya, 3
3, Petya, 9
4, Igor,  4
5, Nikita, 2
 
tasks (assignee_id):
1×3, 2×2, 3×3, 5×6  -- 3, 2, 3, 6 задач у соответствующих сотрудников

Ожидаем: Vasya(id=2) — 2, Igor — 0.

Решение

Подход

Главное — LEFT JOIN, чтобы сотрудники без задач не пропали.

SELECT
  e.emp_name,
  COUNT(t.id) AS tasks_in_progress
FROM employee e
LEFT JOIN tasks t ON t.assignee_id = e.id
GROUP BY e.id, e.emp_name
HAVING COUNT(t.id) < 3
ORDER BY tasks_in_progress, e.emp_name;

Почему COUNT(t.id), а не COUNT(*)

При LEFT JOIN, если у сотрудника нет задач, в результирующей строке t.* все NULL, но COUNT(*) = 1 (строка-то есть). COUNT(t.id) корректно считает 0 при NULL.

Почему GROUP BY по e.id

В таблице employee есть два сотрудника с именем Vasya (id 1 и id 2). Если сгруппировать только по emp_name, мы их склеим — получим Vasya с 5 задачами вместо двух разных людей. Группировка по PK с выводом имени — обязательна.

Ожидаемый результат

Vasya(id=1) с 3 задачами не проходит фильтр.

Подводные камни

1. INNER JOIN потеряет сотрудников без задач (Igor выпадет).
2. COUNT(*) после LEFT JOIN даст 1 вместо 0 для безработных.
3. HAVING vs WHERE. WHERE COUNT(...) < 3 — синтаксическая ошибка. Агрегатные условия — только в HAVING.
4. Дубль имени. Два Vasya — реальная проверка внимательности; если группировать по имени, ответ некорректен.
5. Что значит «в работе». В таблице tasks нет статуса. Если бы был status, нужно было бы фильтровать WHERE status = 'in_progress'.

Эталонный ответ

SELECT e.emp_name, COUNT(t.id) AS tasks_in_progress
FROM employee e
LEFT JOIN tasks t ON t.assignee_id = e.id
GROUP BY e.id, e.emp_name
HAVING COUNT(t.id) < 3;

Ключи: LEFT JOIN, COUNT(t.id) (а не *), группировка по PK сотрудника, HAVING.