채널별 ARPPU와 순위 변동

난이도 어려움 · 권장 20분 · 출력 컬럼 month, channel, arppu, rnk, prev_rnk, rank_change

문제

월별·채널별 ARPPU(결제 유저 1인당 평균 결제액)를 구하고, 각 월 안에서 ARPPU 순위를 매긴 뒤 전월 대비 순위 변동을 구하세요. · ARPPU = 그 달 결제액 합계 / 그 달 결제한 순 유저 수, 소수점 둘째 자리 반올림 · 순위는 ARPPU 내림차순, 동점이면 같은 순위(RANK) · rank_change = 전월 순위 - 이번달 순위 (오르면 양수). 첫 달은 NULL · 월 오름차순, 순위 오름차순으로 정렬하세요.

정렬 month ASC, rnk ASC

쉽게 설명하면

반별 평균 점수로 등수를 매기고, 지난달 등수보다 올랐나 내렸나를 본다.

월	채널	평균	등수	지난달 등수	변동
1월	referral	23,000	1	(없음)	(없음)
2월	referral	23,200	1	1	0
2월	sns	16,900	4	3	-1 (내려감)

변동 = 지난달 등수 − 이번달 등수. 등수가 1→4 로 내려가면 음수.

이렇게 푼다

월·채널별 평균을 먼저 만든다.
월 안에서 등수를 매긴다 — RANK() OVER (PARTITION BY 월 ORDER BY 평균 DESC).
채널별로 지난달 등수를 끌어온다 — LAG(등수) OVER (PARTITION BY 채널 ORDER BY 월).
지난달 등수 − 이번달 등수.

이것만 피하면 된다

묶는 기준이 두 번 바뀐다. 등수를 매길 때는 월로 묶고, 지난달 등수를 끌어올 때는 채널로 묶는다. 둘을 같게 쓰면 전부 틀린다.
첫 달은 지난달 등수가 NULL 이고, 변동도 NULL 이다.

자세히

묻는 것 · 윈도우를 두 단계로 겹쳐 쓸 줄 아는가.

풀이 순서

월×채널 ARPPU 를 먼저 집계.
월 안에서 RANK() → 파티션은 월.
채널 기준으로 LAG(rnk) → 파티션은 채널. 파티션 기준이 다르다.
윈도우 결과를 다시 윈도우에 넣으려면 한 단계 감싸야 한다.

정답 (MySQL)

WITH a AS (
  SELECT DATE_FORMAT(p.paid_date, '%Y-%m-01') AS mm, u.channel,
         ROUND(SUM(p.amount) / COUNT(DISTINCT p.user_id), 2) AS arppu
  FROM payments p JOIN users u ON u.user_id = p.user_id
  GROUP BY mm, u.channel
),
r AS (
  SELECT mm, channel, arppu,
         RANK() OVER (PARTITION BY mm ORDER BY arppu DESC) AS rnk
  FROM a
)
SELECT DATE_FORMAT(mm, '%Y-%m') AS month, channel, arppu, rnk,
       LAG(rnk) OVER (PARTITION BY channel ORDER BY mm)       AS prev_rnk,
       LAG(rnk) OVER (PARTITION BY channel ORDER BY mm) - rnk AS rank_change
FROM r
ORDER BY mm, rnk;

함정

같은 SELECT 안에서 RANK 를 LAG 에 넣을 수 없다. CTE 로 한 단계 감싼다. 이 문제가 묻는 게 바로 이것.
순위는 월로 파티션, 변동은 채널로 파티션. 둘을 같게 쓰면 전부 틀린다.
rank_change 부호 방향을 문제대로. 여기서는 ‘전월 − 이번달’이라 오르면 양수.
ARPPU 분모는 COUNT(DISTINCT user_id). 결제 건수가 아니다.

이렇게 변형된다

“전월 대비 매출 순위 변동”, “주간 랭킹 변화” 전부 같은 2단 구조.