MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Users (`id` INTEGER, `activated` INTEGER);
INSERT INTO Users (`id`, `activated`)VALUES
  ('1', '1'),
  ('2', '1'),
  ('3', '0'),
  ('4', '1'),
  ('5', '1'),
  ('6', '1'),
  ('7', '1'),
  ('8', '0'),
  ('9', '1');

Records: 9  Duplicates: 0  Warnings: 0


CREATE TABLE Follow (`follower_id` INTEGER, `following_id` INTEGER);
INSERT INTO Follow (`follower_id`, `following_id`) VALUES
  ('2', '1'), ('3', '1'), ('4', '1'), ('5', '1'), ('6', '1'), ('7', '1'), ('8', '1'),
  ('1', '2'), ('3', '2'), ('4', '2'), ('5', '2'), ('6', '2'),
  ('9', '5'),
  ('3', '4');

Records: 14  Duplicates: 0  Warnings: 0


CREATE TABLE Blocking (`blocking_id` INTEGER, `blocked_id` INTEGER);
INSERT INTO Blocking (`blocking_id`, `blocked_id`) VALUES
  ('2', '4');
WITH cte AS (
  SELECT f.follower_id user_id
  FROM Follow f
  INNER JOIN Users u ON u.id = f.follower_id AND u.activated
  LEFT JOIN Blocking b ON b.blocking_id = f.following_id AND b.blocked_id = f.follower_id 
  WHERE f.following_id = 2 AND b.blocked_id IS NULL  
)
SELECT c1.user_id, 
       COUNT(c2.user_id) mutual_followers 
FROM cte c1 
LEFT JOIN Follow f ON f.following_id = c1.user_id
LEFT JOIN Blocking b ON b.blocking_id = f.following_id AND b.blocked_id = f.follower_id
LEFT JOIN cte c2 ON c2.user_id = f.follower_id
WHERE b.blocked_id IS NULL 
GROUP BY c1.user_id;


user_id
mutual_followers


1
2

5
0

6
0