UserAndPopularFollower.sql

/* get total number of followers*/
SELECT  PERSON_ID, COUNT(*) AS NUM_FOLLOWERS
FROM FOLLOWERS
GROUP BY PERSON_ID order by PERSON_ID asc; 

/* test query above*/
select * from FOLLOWERS where PERSON_ID =1 ORDER BY FOLLOWER_PERSON_ID ASC;



SELECT t1.person_id, t1.follower_person_id, 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
ORDER BY t1.person_id , CNT DESC;

SELECT t1.person_id,   MAX( t2.cnt ) AS MAX_NUM_FOLLOWERS
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
GROUP BY (T1.PERSON_ID) ORDER BY t1.person_id ;


SELECT A.* from (

SELECT t1.person_id, t1.follower_person_id, 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
ORDER BY t1.person_id , CNT DESC 

) as A inner join (

SELECT t1.person_id,   MAX( t2.cnt ) AS MAX_NUM_FOLLOWERS
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
GROUP BY (T1.PERSON_ID) ORDER BY t1.person_id

) as B on A.person_id = b.person_id AND A.cnt = B.MAX_NUM_FOLLOWERS;