Question

Write SQL query for the user follower and following count, select user's id, first name, last name and age

Answer 1

SELECT Table1.id AS ID
, Table1.first_name AS Fname
   , Table1.last_name AS Lname
   , Table2.FollowingCount AS FollowingCount
   , Table2.FollowedCount AS FollowedCount
FROM
(
       SELECT T1.*
       FROM (
               SELECT *
               FROM user
               WHERE DOB >= date_sub(curdate(), interval 21 year)
           ) AS T1
           ) AS Table1
JOIN
(
       SELECT T2.*
       FROM
       (
       SELECT id, count(following_user_id) AS FollowingCount, count(followed_user_id) AS FollowedCount
       FROM user_follow
       GROUP BY id
       ) AS T2
   ) AS Table2
ON(Table1.id == Table2.id)
ORDER BY Table1.first_name ASC, Table1.last_name ASC;