A/B 테스트 그룹 집계 (상위 1% 제외)

난이도 보통 · 권장 14분 · 출력 컬럼 group_name, user_count, group_total_amount, arpu

문제

experiment_users 의 모든 참가자를 대상으로 그룹별 지표를 구하세요. group_name user_count 이상치를 뺀 유저 수 group_total_amount 이상치를 뺀 총 거래 금액 합계 arpu group_total_amount / user_count, 소수점 없이 반올림 · user_trades_summary 에 행이 없는 참가자는 거래 금액 0 으로 봅니다. · 이상치 판정은 '거래 금액 0 처리까지 끝낸 뒤' 그룹별로 합니다. PERCENT_RANK() OVER (PARTITION BY group_name ORDER BY 금액) > 0.99 인 유저를 뺍니다. · group_name 오름차순으로 정렬하세요.

정렬 group_name ASC

쉽게 설명하면

반 평균 낼 때 혼자 만점인 애를 빼고 계산. 단, 빼는 순서가 중요하다.

이 순서를 바꾸면 답이 달라진다.

①	거래 없는 참가자를 0 으로 채운다
②	그 상태에서 그룹별로 등수를 매긴다
③	맨 위를 자른다
④	남은 사람으로 평균

①을 빼고 ②를 하면 모집단이 달라져서 잘려나갈 사람도 달라진다.

이렇게 푼다

참가자 명단을 왼쪽에 두고 거래 금액을 붙이고 COALESCE(금액, 0).
그 결과 위에서 PERCENT_RANK() OVER (PARTITION BY 그룹 ORDER BY 금액).
한 번 더 감싸서 바깥에서 WHERE 등수 <= 0.99.
남은 것으로 인원·합계·평균.

이것만 피하면 된다

등수 함수는 WHERE 에 못 쓴다. WHERE PERCENT_RANK() OVER (…) <= 0.99 는 문법 오류다. 실행 순서가 WHERE 먼저, 등수 나중이라서 그렇다. 한 단 감싸는 것 외에 방법이 없다.
거래 없는 참가자를 빼면 안 된다. 실험에 참가한 사람은 거래를 안 했어도 참가자다. 분모를 임의로 줄이면 A/B 결과를 못 쓴다.
알아 둘 것: PERCENT_RANK 는 맨 위가 항상 정확히 1.0 이다. 그래서 > 0.99 는 '상위 1%' 가 아니라 거의 항상 맨 위 한 명만 자른다. 문제가 그렇게 시켰으니 그대로 풀되, 이게 허술한 정의라는 건 알고 있어라. 면접에서 물으면 '진짜 상위 1% 는 NTILE(100) 로 자른다' 고 답하면 된다.

자세히

묻는 것 · 순서가 생명인 문제. ①0 채우기 → ②순위 매기기 → ③자르기 → ④집계. 이 순서를 바꾸면 답이 달라진다.

풀이 순서

실험 참가자 테이블을 왼쪽에 두고 거래 요약을 LEFT JOIN, COALESCE(금액, 0) 으로 0 을 채운다. 분모는 참가자 수여야 한다.
그 결과 위에서 PERCENT_RANK() OVER (PARTITION BY group_name ORDER BY amt). 0 을 채운 뒤에 순위를 매기는 것이 중요하다 — 0 인 유저도 모집단의 일부다.
WHERE pr <= 0.99 로 이상치를 떨어뜨린다. 윈도우 함수는 WHERE 에서 못 쓰므로 반드시 서브쿼리/CTE 로 한 단 감싸야 한다.
남은 행으로 COUNT(*), SUM(amt), 그리고 ROUND(SUM(amt)/COUNT(*), 0).

정답 (MySQL)

WITH j AS (
  SELECT e.user_id, e.group_name,
         COALESCE(s.total_trading_amount, 0) AS amt
  FROM experiment_users e
  LEFT JOIN user_trades_summary s ON s.user_id = e.user_id
),
p AS (
  SELECT group_name, amt,
         PERCENT_RANK() OVER (PARTITION BY group_name ORDER BY amt) AS pr
  FROM j
)
SELECT group_name,
       COUNT(*) AS user_count,
       SUM(amt) AS group_total_amount,
       ROUND(SUM(amt) / COUNT(*), 0) AS arpu
FROM p
WHERE pr <= 0.99
GROUP BY group_name
ORDER BY group_name;

함정

윈도우 함수는 WHERE 에 못 쓴다. WHERE PERCENT_RANK() OVER (...) <= 0.99 는 문법 오류다. 실행 순서가 WHERE → 윈도우 함수이기 때문이다. 한 단 감싸는 것 외에 방법이 없다.
LEFT JOIN 을 INNER JOIN 으로 쓰면 유저 수가 깎인다. 거래가 없는 참가자도 실험 참가자다. A/B 테스트에서 분모를 임의로 줄이면 그 결과는 못 쓴다. 「결제없음」 데이터셋이 이걸 잡는다.
0 을 채우기 전에 순위를 매기면 다른 답이 나온다. 거래 없는 유저를 빼고 순위를 매기면 모집단이 달라지고 잘려 나가는 사람도 달라진다. 문제가 지정한 순서를 따르라.
PERCENT_RANK 의 정의를 알고 써라. (순위 − 1) / (전체 − 1) 이므로 최솟값은 항상 0, 최댓값은 항상 1 이다. 그래서 > 0.99 는 '상위 1%' 가 아니라 거의 항상 최댓값 행만 떨어뜨린다. 그룹에 2명뿐이면 1명이 날아간다. 면접에서 물으면 '진짜 상위 1% 는 NTILE(100) 이나 PERCENTILE_CONT 로 임계값을 구해 자른다' 고 답하라.
최댓값이 동점이면 둘 다 빠진다. PERCENT_RANK 는 동점에 같은 값을 주므로 공동 1위가 둘이면 둘 다 1.0 이다. 「동점」 데이터셋이 이걸 잡는다.
ROUND(..., 0) 은 정수가 아니라 소수 0자리다. 엔진에 따라 150 또는 150.0 으로 보인다. 정수형으로 받아야 하면 CAST(ROUND(...) AS SIGNED) 까지 써라.

이렇게 변형된다

“상·하위 1% 양쪽 제거”면 pr BETWEEN 0.01 AND 0.99. “중앙값 비교”면 ROW_NUMBER + COUNT() OVER 패턴. “전환율 비교”면 COUNT(DISTINCT 전환유저)/COUNT(DISTINCT 참가자) 로 분모를 참가자 전체로 둔다 (intention-to-treat).