Returns a list of all users, paired with their most “popular” follower. , MySQL

Language: Layout:

DROP TABLE IF EXISTS followers;
DROP TABLE IF EXISTS person;

CREATE TABLE followers
    (`id` int, `person_id` int, `follower_person_id` int)
;
    
INSERT INTO followers
    (`id`, `person_id`, `follower_person_id`)
VALUES
    (1, 1, 2),
    (2, 1, 3),
    (3, 2, 1),
    (4, 2, 4),
    (5, 3, 1),
    (6, 3, 2),
    (7, 3, 4),
    (8, 4, 3)
;

CREATE TABLE person
    (`id` int, `name` varchar(8))
;
    
INSERT INTO person
    (`id`, `name`)
VALUES
    (1, 'John'),
    (2, 'Ali'),
    (3, 'Rohn'),
    (4, 'Veronica')
;

SELECT person_name, follower_name, cnt
FROM (
   SELECT person_name, follower_name, cnt,
          @rn := IF(@pname = person_name, @rn + 1,
                    IF(@pname := person_name, 1, 1)) AS rn
   FROM (
      SELECT t3.name AS person_name, t4.name AS follower_name, t2.cnt
      FROM followers AS t1
      JOIN (
         SELECT person_id, COUNT(*) AS cnt
         FROM followers
         GROUP BY person_id 
      ) AS t2  ON t1.follower_person_id = t2.person_id
      JOIN person AS t3 ON t1.person_id = t3.id
      JOIN person AS t4 ON t1.follower_person_id = t4.id   
   ) AS x
   CROSS JOIN (SELECT @rn := 0, @pname := '') AS vars
   ORDER BY person_name, cnt DESC) AS v
wHERE v.rn = 1;

DROP TABLE person;
DROP TABLE followers;


absolute service time: 0,47 sec

Returns a list of all users, paired with their most “popular” follower...