SQL Get users with max occurrences in table

Given the following table transactions, which records the IDs of sellers and buyers who had a transaction, I would like to determine the user who was involved in the highest number of transactions and the number of transactions that user engaged in.

seller_id	buyer_id	date
1	4	2020-01-02
2	1	2020-01-03
3	2	2020-02-16
4	2	2020-02-22
4	3	2020-03-05

The desired output is this:

ID	n_trans
2	3
4	3

Because user 2 had a total of 3 transactions (1 as seller, 2 as buyer) and user 4 also had 3 transactions (2 as seller, 1 as buyer). It can be assumed that a user cannot be a buyer and seller in the same transaction, and that each buyer-seller combination is not duplicated.

What SQL query will get me this? I would not find any similar questions online. Thanks in advance!

Answer

You can unpivot, aggregate, and use window functions:

select id, cnt
from (select id, count(*) as cnt,
             rank() over (order by count(*) desc) as seqnum
      from ((select seller_id as id, date from t) union all
            (select buyer_id, date from t)
           ) i
      group by id
     ) i
where seqnum = 1;