[SQL/오답] 유저별 지배적 반응과 비율 계산 (Leetcode3808 Medium)

3808. Find Emotionally Consistent Users

1. Problem

유저가 남긴 리액션 데이터를 분석하여 '정서적으로 일관된 유저'를 식별해야 한다.

• 조건 1: 최소 5개 이상의 서로 다른 콘텐츠에 리액션을 남긴 유저여야 한다.
• 조건 2: 특정 한 종류의 리액션이 전체 리액션 중 60% 이상을 차지해야 한다.
• 결과: 일관성 비율(reaction_ratio) 내림차순, 유저 ID 오름차순으로 정렬한다.

---

2. Solution: CTE 분리를 통한 분자/분모 산출

전체 리액션 수(분모)와 각 감정별 수(분자)를 각각 구한 뒤, 윈도우 함수를 사용하여 가장 빈도가 높은 감정을 선택한다.

 

-- 1. 유저별 전체 리액션 수 계산 (분모 및 최소 조건 5개 필터링)
WITH total AS (
    SELECT user_id, COUNT(DISTINCT content_id) AS total_quantity
    FROM reactions 
    GROUP BY user_id
    HAVING COUNT(DISTINCT content_id) >= 5 
),
-- 2. 유저별 + 감정별 리액션 빈도 계산 (분자)
reaction AS (
    SELECT user_id, 
           reaction, 
           COUNT(*) AS quantity
    FROM reactions 
    GROUP BY user_id, reaction
)

-- 3. 가장 빈도가 높은 감정 추출 및 비율 계산
SELECT t1.user_id, 
       t1.reaction AS dominant_reaction, 
       ROUND(t1.quantity / t2.total_quantity, 2) AS reaction_ratio 
FROM (
    SELECT user_id, 
           reaction,
           quantity,
           -- 유저별로 가장 많이 나타난 감정 순으로 번호 부여
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY quantity DESC) AS num
    FROM reaction
) t1
JOIN total t2 ON t1.user_id = t2.user_id  
WHERE t1.num = 1 -- 가장 많이 나타난 감정만 선택
      AND t1.quantity / t2.total_quantity >= 0.6 -- 60% 이상 일관성 조건
ORDER BY reaction_ratio DESC, t1.user_id ASC;

---

3. Takeaway: 이 쿼리의 객관적 분석

• 분석 함수와 필터링의 순서:
  • ROW_NUMBER()를 사용하여 유저별로 가장 빈번한 리액션을 '1번'으로 마킹하였다. 이때 PARTITION BY에 reaction까지 넣으면 모든 행이 1번이 되므로, PARTITION BY user_id ORDER BY quantity DESC로 수정하여 유저 내에서 감정 간 순위를 매기는 것이 정확하다.
• 비율 계산의 정석:
  • 전체 개수(total_quantity)를 별도의 CTE로 분리하여 조인하는 방식은 데이터의 정합성을 유지하면서 연산 과정을 명확히 보여준다.
• COUNT(DISTINCT content_id)의 세심함:
  • 문제에서 '서로 다른 콘텐츠'라는 조건을 충족하기 위해 DISTINCT를 사용한 점이 훌륭하다. 이는 한 콘텐츠에 여러 번 반응할 수 있는 실제 데이터 환경을 고려한 설계이다.

---

💡 실무 팀

 "이 로직은 소셜 미디어 플랫폼에서 유저의 성향을 파악하거나, 악성 유저(예: 모든 글에 '싫어요'만 누르는 유저)를 탐지할 때 유용하게 쓰인다. 마케팅 측면에서는 특정 감정에 편향된 유저에게 맞춤형 콘텐츠를 추천하는 기초 자료가 된다."