[SQL문제풀기 - Advent of SQL 2025 🎅] 인플루언서 마케팅 후보 찾기

문제

solvesql.com

https://solvesql.com/problems/influencer-marketing-candidates/

내가 작성한 정답

MySQL, PostgreSQL

with a as ((select user_a_id user_id, user_b_id f_id from edges)
            union all
           (select user_b_id user_id, user_a_id f_id from edges)),
     b as (select user_id, count(*) friends
          from a 
          group by user_id)
select a.user_id, max(c.friends) friends, sum(b.friends) friends_of_friends,
       round(sum(b.friends*1.0/c.friends),2) ratio
from a a
join b b on a.f_id = b.user_id
join b c on c.user_id = a.user_id
where a.user_id in (select user_id from b where friends >= 100)
group by a.user_id 
order by 4 desc
limit 5;

SQLite

with a as (select user_a_id user_id, user_b_id f_id from edges
            union all
           select user_b_id user_id, user_a_id f_id from edges),
     b as (select user_id, count(*) friends
          from a 
          group by user_id)
select a.user_id, max(c.friends) friends, sum(b.friends) friends_of_friends,
       round(sum(b.friends*1.0/c.friends),2) ratio
from a a
join b b on a.f_id = b.user_id
join b c on c.user_id = a.user_id
where a.user_id in (select user_id from b where friends >= 100)
group by a.user_id 
order by 4 desc
limit 5;