Вывести количество задач у каждого сотрудника

77. Вывести количество задач у каждого сотрудника

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

Даны таблицы сотрудников users и задач tasks.

Необходимо вывести:

идентификатор сотрудника;
идентификатор его департамента;
количество задач, назначенных этому сотруднику.

Сотрудники, у которых нет задач, также должны присутствовать в результате с количеством задач 0.

Структура таблиц:

CREATE TABLE users
(
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    head_user_id BIGINT REFERENCES users(id),
    department_id BIGINT
);

CREATE TABLE tasks
(
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    user_id BIGINT REFERENCES users(id),
    processed BOOLEAN
);

Спойлеры к решению

Подсказки

💡 Таблицы users и tasks связаны через поле tasks.user_id.

💡 Чтобы посчитать количество задач для каждого сотрудника, можно использовать агрегатную функцию COUNT().

💡 Если использовать обычный INNER JOIN, сотрудники без задач не попадут в результат.

💡 Для сохранения всех сотрудников необходимо использовать LEFT JOIN.

Решение

Соединим таблицу сотрудников с таблицей задач по идентификатору сотрудника:

SELECT
    u.id AS user_id,
    u.department_id,
    COUNT(t.id) AS task_count
FROM users u
LEFT JOIN tasks t
    ON t.user_id = u.id
GROUP BY
    u.id,
    u.department_id;

Здесь:

LEFT JOIN tasks t
    ON t.user_id = u.id

позволяет сохранить в результате всех сотрудников, даже если для них нет соответствующих записей в таблице tasks.

COUNT(t.id)

подсчитывает количество задач конкретного сотрудника.

Важно использовать именно:

COUNT(t.id)

а не:

COUNT(*)

При LEFT JOIN для сотрудника без задач всё равно будет сформирована строка результата. COUNT(*) посчитает такую строку и вернёт 1, тогда как COUNT(t.id) игнорирует NULL и корректно вернёт 0.

Например, если имеются данные:

users:

id | department_id
---+--------------
1  | 10
2  | 10
3  | 20

и:

tasks:

id | user_id
---+--------
1  | 1
2  | 1
3  | 2

результат будет:

user_id | department_id | task_count
--------+---------------+-----------
1       | 10            | 2
2       | 10            | 1
3       | 20            | 0

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