Forum

Posted by gurpreet, Oct. 29, 2021, 8:37 p.m.

SQL#97

I have the following solution:
WITH sender as
(
SELECT sender_id as id,
count(distinct recipient_id) as cnt
FROM accepted_request
GROUP BY id
),
recipient as
(
SELECT recipient_id as id,
count(distinct sender_id) as cnt
FROM accepted_request
GROUP BY id
),
cumulative as
(
select * from sender
union
select * from recipient
),

top as
(
SELECT id,
sum(cnt) as count_id
from cumulative
group by id
)
select id from top
order by count_id desc
limit 1
I am trying to find the mistake in this code, can anyone help? Appreciate it, thanks!

Answers

Hey Gurpreet, a user can accept a request multiple times, I think you need to union first, then count distinct freindships.
For example:
select * from
accepted_request
where sender_id =1000043
or recipient_id = 1000043
order by 1,2;
You will see there are duplicates.
sender_id recipient_id acceptance_dt
1000003 1000043 2021-01-08
1000003 1000043 2021-01-07
SQLPad user avatar

Leon (949)

Nov. 1, 2021, 5:45 p.m.