MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE users (
  id INT UNSIGNED PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  email VARCHAR(20) NOT NULL
);
INSERT INTO users VALUES
  (1, 'user1', 'user1@example.com'),
  (2, 'user2', 'user2@example.com'),
  (3, 'user3', 'user3@example.com'),
  (4, 'user4', 'user4@example.com');

CREATE TABLE tasks (
  id INT UNSIGNED PRIMARY KEY,
  title VARCHAR(20) NOT NULL,
  status ENUM('T', 'D') NOT NULL
);
INSERT INTO tasks VALUES
  (1, 'task1', 'D'),
  (2, 'task2', 'D'),
  (3, 'task3', 'D'),
  (4, 'task4', 'T'),
  (5, 'task5', 'T'),
  (6, 'task6', 'D'),
  (7, 'task7', 'T');

CREATE TABLE task_user (
  task_id INT UNSIGNED,
  user_id INT UNSIGNED,
  team_lead TINYINT UNSIGNED DEFAULT 0,
  PRIMARY KEY (task_id, user_id),
  INDEX (user_id, task_id),
  FOREIGN KEY (task_id) REFERENCES tasks (id),
  FOREIGN KEY (user_id) REFERENCES users (id)
);
INSERT INTO task_user VALUES
  (1, 1, 1),
  (2, 1, 1),
  (3, 1, 1),
  (4, 1, 1),
  (5, 1, 1),
  (5, 2, 0),
  (5, 3, 0),
  (6, 1, 1),
  (6, 2, 0),
  (6, 3, 0),
  (7, 1, 0),
  (7, 2, 0),
  (7, 3, 1);

Records: 4  Duplicates: 0  Warnings: 0



Records: 7  Duplicates: 0  Warnings: 0



Records: 13  Duplicates: 0  Warnings: 0


# Current counts don't make sense, as you are counting all users if current userId in subquery result
SET @userId = 1;

select count(*)
as hasActiveTeamLeadTasks
from users
where @userId in (select distinct task_user.user_id
                  from task_user,
                       tasks
                  where tasks.status = 'T'
                    and task_user.task_id = tasks.id
                    and task_user.team_lead = 1);

SET @userId = 2;

select count(*)
as hasActiveTeamLeadTasks
from users
where @userId in (select distinct task_user.user_id
                  from task_user,
                       tasks
                  where tasks.status = 'T'
                    and task_user.task_id = tasks.id
                    and task_user.team_lead = 1);

SET @userId = 3;

select count(*)
as hasActiveTeamLeadTasks
from users
where @userId in (select distinct task_user.user_id
                  from task_user,
                       tasks
                  where tasks.status = 'T'
                    and task_user.task_id = tasks.id
                    and task_user.team_lead = 1);


hasActiveTeamLeadTasks


4




hasActiveTeamLeadTasks


0




hasActiveTeamLeadTasks


4


SELECT
    u.id AS userID, u.name AS userName, u.email AS userEmail,
    SUM(t.status = 'T') AS activeTasksCount,
    SUM(t.status = 'D') AS completedTasksCount,
    SUM(t.status = 'T' AND tu.team_lead = 1) AS hasActiveTeamLeadTasks,
    JSON_ARRAYAGG(JSON_OBJECT('id', t.id, 'title', t.title)) AS taskJson
FROM users u
LEFT JOIN task_user tu ON u.id = tu.user_id
LEFT JOIN tasks t ON tu.task_id = t.id
GROUP BY u.id
ORDER BY u.id;


userID
userName
userEmail
activeTasksCount
completedTasksCount
hasActiveTeamLeadTasks
taskJson


1
user1
user1@example.com
3
4
2
[{"id": 1, "title": "task1"}, {"id": 2, "title": "task2"}, {"id": 3, "title": "task3"}, {"id": 4, "title": "task4"}, {"id": 5, "title": "task5"}, {"id": 6, "title": "task6"}, {"id": 7, "title": "task7"}]

2
user2
user2@example.com
2
1
0
[{"id": 5, "title": "task5"}, {"id": 6, "title": "task6"}, {"id": 7, "title": "task7"}]

3
user3
user3@example.com
2
1
1
[{"id": 5, "title": "task5"}, {"id": 6, "title": "task6"}, {"id": 7, "title": "task7"}]

4
user4
user4@example.com
null
null
null
[{"id": null, "title": null}]

userID	userName	userEmail	activeTasksCount	completedTasksCount	hasActiveTeamLeadTasks	taskJson
1	user1	user1@example.com	3	4	2	[{"id": 1, "title": "task1"}, {"id": 2, "title": "task2"}, {"id": 3, "title": "task3"}, {"id": 4, "title": "task4"}, {"id": 5, "title": "task5"}, {"id": 6, "title": "task6"}, {"id": 7, "title": "task7"}]
2	user2	user2@example.com	2	1	0	[{"id": 5, "title": "task5"}, {"id": 6, "title": "task6"}, {"id": 7, "title": "task7"}]
3	user3	user3@example.com	2	1	1	[{"id": 5, "title": "task5"}, {"id": 6, "title": "task6"}, {"id": 7, "title": "task7"}]
4	user4	user4@example.com	null	null	null	[{"id": null, "title": null}]