Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE requestaccepted (
  requester_id int
, accepter_id int
, accept_date timestamp
);

INSERT INTO requestaccepted VALUES
  (1, 2, '2016-06-03 02:00:00')
, (1, 3, '2016-06-08 02:00:00')
, (2, 3, '2016-06-08 02:00:00')
, (3, 4, '2016-06-09 02:00:00')
, (1, 4, '2016-06-09 02:00:00')  -- another friend for ID 1 to create a tie
;
5 rows affected

-- Pick a single winner
SELECT a.id, count(*) AS num
FROM  (
   SELECT requester_id AS id FROM requestaccepted
   UNION ALL
   SELECT accepter_id  AS id FROM requestaccepted
   ) a
GROUP  BY a.id
ORDER  BY count(*) DESC, id  --  pick smallest id in case of ties
LIMIT  1;


id
num


1
3


-- If you must avoid LIMIT / FETCH FIRST, the window function row_number() is a (more expensive!) alternative:
SELECT id, num
FROM  (
   SELECT a.id, count(*) AS num
        , row_number() OVER (ORDER BY count(*) DESC) AS rn
   FROM  (
      SELECT requester_id AS id FROM requestaccepted
      UNION ALL
      SELECT accepter_id  AS id FROM requestaccepted
      ) a
   GROUP BY a.id
   ) sub
WHERE  rn = 1;


id
num


3
3


-- Return all winners
SELECT a.id, count(*) AS num
FROM  (
   SELECT requester_id AS id FROM requestaccepted
   UNION ALL
   SELECT accepter_id  AS id FROM requestaccepted
   ) a
GROUP  BY a.id
ORDER  BY count(*) DESC  --  additional ORDER BY expressions would resolve tie
FETCH  FIRST 1 ROWS WITH TIES;


id
num


3
3

1
3


-- If you must avoid LIMIT / FETCH FIRST, the window function rank() is a (more expensive!) alternative:
SELECT id, num
FROM  (
   SELECT a.id, count(*) AS num
       , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM  (
      SELECT requester_id AS id FROM requestaccepted
      UNION ALL
      SELECT accepter_id  AS id FROM requestaccepted
      ) a
   GROUP  BY a.id
   ) sub
WHERE  rnk = 1
ORDER  BY id; -- optional


id
num


1
3

3
3