LeetCode - 3564. Seasonal Sales Analysis (Oracle)

Medium - CTE, Inline View

RunTime : 605 ms

문제

Write a solution to find the most popular product category for each season. The seasons are defined as:

• Winter: December, January, February
• Spring: March, April, May
• Summer: June, July, August
• Fall: September, October, November

The popularity of a category is determined by the total quantity sold in that season. If there is a tie, select the category with the highest total revenue (quantity × price).

Return the result table ordered by season in ascending order.

The result format is in the following example.

풀이

• Medium 치고 난이도가 상당했음, 내가 오랜만에 풀어서 그런가

1. S_CTE에서는 각 데이터들을 월별로 필터링해 계절로 그룹화하고
   product_id를 기준으로 join해, total_quantity와 total_revenue를 구한다.

2. 그럼 이렇게 각 계절별, 카테고리별로 묶인 값들에 대해 순위를 매겨야한다.

3. 메인쿼리에서는 S_CTE에서 값을 가져오면서 ROW_NUMBER()를 통해 순위를 매긴다.

4. 이때 ORDER BY로 문제에서 제시한 조건을 넣고, rn = 1인 row만 가져온다.

-- 1단계: 계절별, 카테고리별 판매 데이터 집계 (CTE 사용)
WITH S_CTE AS (
    SELECT
        CASE
            WHEN TO_CHAR(a.sale_date, 'MM') IN ('12', '01', '02') THEN 'Winter'
            WHEN TO_CHAR(a.sale_date, 'MM') IN ('03', '04', '05') THEN 'Spring'
            WHEN TO_CHAR(a.sale_date, 'MM') IN ('06', '07', '08') THEN 'Summer'
            ELSE 'Fall'
        END AS season,
        b.category,
        SUM(a.quantity) AS total_quantity,
        SUM(a.quantity * a.price) AS total_revenue
    FROM
        sales a,
        products b
    where   1=1
    and     a.product_id = b.product_id
    GROUP BY
        CASE
            WHEN TO_CHAR(a.sale_date, 'MM') IN ('12', '01', '02') THEN 'Winter'
            WHEN TO_CHAR(a.sale_date, 'MM') IN ('03', '04', '05') THEN 'Spring'
            WHEN TO_CHAR(a.sale_date, 'MM') IN ('06', '07', '08') THEN 'Summer'
            ELSE 'Fall'
        END,
        b.category
)
-- 2단계: 순위 매기기 및 최상위 카테고리 필터링
SELECT
    season,                                
    category,                              
    total_quantity, 
    total_revenue                         
FROM (
    SELECT
        season,
        category,
        total_quantity,
        total_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY season             
            ORDER BY total_quantity DESC, 
                     total_revenue DESC         
        ) as rn
    FROM
        S_CTE
) RankedData
WHERE
    rn = 1 -- 각 계절별 1위 카테고리만 선택
ORDER BY 1;