카테고리별 최고 매출 상품 찾기 — GROUP BY와 ROW_NUMBER로 푸는 SQL 문제

SQL 문제를 풀다 보면
단순히 데이터를 조회하는 것보다, 집계한 결과 안에서 다시 순위를 매겨야 하는 문제가 더 어렵게 느껴질 때가 있습니다.

이번 글에서는
카테고리별로 가장 매출이 높은 상품을 찾는 문제를 정리해보겠습니다.

이 문제는 JOIN, GROUP BY, SUM 같은 기본기 위에
ROW_NUMBER() 같은 윈도우 함수 감각까지 함께 보는 문제입니다.

문제

온라인 쇼핑몰에는 상품 정보와 주문 상세 정보가 아래와 같이 저장되어 있습니다.

PRODUCTS

컬럼명	설명
product_id	상품 ID
product_name	상품 이름
category	카테고리명

ORDER_ITEMS

컬럼명	설명
order_item_id	주문 상세 ID
product_id	상품 ID
quantity	주문 수량
unit_price	주문 당시 상품 1개 가격

각 카테고리마다 총매출이 가장 높은 상품의 이름과 총매출을 조회하는 SQL문을 작성해보겠습니다.

총매출은 아래처럼 계산합니다.

quantity * unit_price

단, 결과는 다음 규칙을 따라야 합니다.

카테고리별로 총매출 1위 상품만 조회합니다
총매출이 같은 상품이 여러 개면, product_id가 더 작은 상품을 선택합니다
결과는 category 오름차순으로 정렬합니다

예시 데이터

PRODUCTS

product_id	product_name	category
1	무선 마우스	전자기기
2	기계식 키보드	전자기기
3	텀블러	생활용품
4	수납 바구니	생활용품
5	독서대	문구

ORDER_ITEMS

order_item_id	product_id	quantity	unit_price
101	1	3	20000
102	1	2	20000
103	2	4	15000
104	3	5	8000
105	4	2	12000
106	4	3	12000
107	5	6	7000

기대 결과는 아래와 같습니다.

category	product_name	total_sales
문구	독서대	42000
생활용품	수납 바구니	60000
전자기기	무선 마우스	100000

처음 떠오르는 생각

이 문제는 한 번에 바로 쓰려고 하면 꼬이기 쉽습니다.

흐름을 먼저 쪼개는 게 좋습니다.

상품별 총매출 계산
카테고리 안에서 매출 순위 매기기
각 카테고리 1위만 남기기

즉, 처음부터 “카테고리별 1위”를 바로 구하려고 하기보다
먼저 상품 단위 매출표를 하나 만든다고 생각하면 훨씬 편합니다.

핵심 아이디어

우선 PRODUCTS와 ORDER_ITEMS를 product_id로 연결합니다.

그다음 상품별로 묶어서 총매출을 계산합니다.

SELECT
    p.product_id,
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM PRODUCTS p
JOIN ORDER_ITEMS oi
    ON p.product_id = oi.product_id
GROUP BY
    p.product_id,
    p.product_name,
    p.category;

이 쿼리를 실행하면 각 상품마다 총매출이 계산된 중간 결과를 얻을 수 있습니다.

예를 들면 이런 느낌입니다.

product_id	product_name	category	total_sales
1	무선 마우스	전자기기	100000
2	기계식 키보드	전자기기	60000
3	텀블러	생활용품	40000
4	수납 바구니	생활용품	60000
5	독서대	문구	42000

이제 각 카테고리 안에서 총매출이 높은 상품부터 순서를 매기면 됩니다.

여기서 ROW_NUMBER()를 사용할 수 있습니다.

ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY total_sales DESC, product_id ASC
)

이 구문 뜻은 이렇습니다.

PARTITION BY category → 카테고리별로 그룹을 나눕니다
ORDER BY total_sales DESC → 매출이 높은 순으로 정렬합니다
product_id ASC → 매출이 같으면 상품 ID가 작은 쪽이 먼저 옵니다

즉, 문제 조건을 그대로 순위 기준에 녹여 넣은 것입니다.

코드

WITH sales_by_product AS (
    SELECT
        p.product_id,
        p.product_name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS total_sales
    FROM PRODUCTS p
    JOIN ORDER_ITEMS oi
        ON p.product_id = oi.product_id
    GROUP BY
        p.product_id,
        p.product_name,
        p.category
),
ranked_products AS (
    SELECT
        product_id,
        product_name,
        category,
        total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY total_sales DESC, product_id ASC
        ) AS rn
    FROM sales_by_product
)
SELECT
    category,
    product_name,
    total_sales
FROM ranked_products
WHERE rn = 1
ORDER BY category ASC;

코드 해설

1) `sales_by_product`에서 상품별 총매출 계산

WITH sales_by_product AS (
    SELECT
        p.product_id,
        p.product_name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS total_sales
    FROM PRODUCTS p
    JOIN ORDER_ITEMS oi
        ON p.product_id = oi.product_id
    GROUP BY
        p.product_id,
        p.product_name,
        p.category
)

여기서는 상품별로 주문 금액을 모두 더해서 total_sales를 만들고 있습니다.

핵심은 이 부분입니다.

SUM(oi.quantity * oi.unit_price)

주문 상세마다 수량 × 가격으로 매출을 만든 뒤, 같은 상품끼리 다 더하는 구조입니다.

2) `ranked_products`에서 카테고리별 순위 계산

ranked_products AS (
    SELECT
        product_id,
        product_name,
        category,
        total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY total_sales DESC, product_id ASC
        ) AS rn
    FROM sales_by_product
)

여기서 각 카테고리 안에서 순위를 매깁니다.

예를 들면 전자기기 카테고리 안에서는:

무선 마우스: 100000 → 1등
기계식 키보드: 60000 → 2등

이런 식으로 번호가 붙습니다.

3) `rn = 1`만 남기기

SELECT
    category,
    product_name,
    total_sales
FROM ranked_products
WHERE rn = 1
ORDER BY category ASC;

카테고리별 1등만 남기면 문제에서 원하는 결과가 됩니다.

실행 예시

이 문제는 단순히 MAX(total_sales)만 구해서는 끝나지 않습니다.

왜냐하면 문제 조건에
“동점이면 product_id가 더 작은 상품을 선택”
이라는 추가 규칙이 있기 때문입니다.

즉, 단순 최대값만 구하면 동점 처리에서 다시 한 번 더 조건을 붙여야 합니다.

반면 ROW_NUMBER()를 쓰면 순위 기준을 한 번에 정리할 수 있어서 훨씬 깔끔합니다.

사람 손으로 푼다고 생각하면 흐름은 이렇습니다.

상품별로 매출을 다 합칩니다
같은 카테고리끼리 모읍니다
매출 높은 순으로 줄 세웁니다
맨 앞 상품만 뽑습니다

SQL도 같은 흐름입니다. 다만 그 과정을 GROUP BY, SUM, ROW_NUMBER()로 표현하는 것뿐입니다.

복잡도

이 문제는 단순 SELECT보다 한 단계 더 복잡합니다.

핵심 흐름은 아래와 같습니다.

JOIN으로 테이블 연결
GROUP BY로 상품별 매출 집계
ROW_NUMBER()로 카테고리별 순위 계산
1위만 조회

즉, 집계하고 끝나는 문제가 아니라, 집계 결과 안에서 다시 순위를 매기는 문제입니다.

이 흐름을 익혀 두면
“부서별 최고 연봉자”, “카테고리별 인기 상품”, “사용자별 최신 주문” 같은 문제로도 연결할 수 있습니다.

정리

이 문제는 JOIN, GROUP BY, SUM 같은 기본기 위에
윈도우 함수까지 함께 연습할 수 있는 SQL 문제입니다.

특히 동점 처리 기준까지 함께 넣어야 하기 때문에,
단순 최대값 조회보다 한 단계 더 실전적인 감각이 필요합니다.

문제를 풀 때는:

먼저 집계 결과를 만든 뒤
그 안에서 순위를 매기고
마지막에 필요한 행만 남긴다

이 순서로 생각하면 훨씬 덜 꼬입니다.

※ 이 글의 문제 상황과 예시는 학습용으로 직접 재구성한 내용입니다.

카테고리별 최고 매출 상품 찾기 — GROUP BY와 ROW_NUMBER로 푸는 SQL 문제

문제

PRODUCTS

ORDER_ITEMS

예시 데이터

PRODUCTS

ORDER_ITEMS

처음 떠오르는 생각

핵심 아이디어

코드

코드 해설

1) sales_by_product에서 상품별 총매출 계산

2) ranked_products에서 카테고리별 순위 계산

3) rn = 1만 남기기

실행 예시

복잡도

정리

함께 보면 좋은 글

태그

1) `sales_by_product`에서 상품별 총매출 계산

2) `ranked_products`에서 카테고리별 순위 계산

3) `rn = 1`만 남기기