카테고리 별 매출 비율

https://solvesql.com/problems/revenue-pct-per-category/

https://solvesql.com/problems/revenue-pct-per-category/

 

 

[풀이]

SELECT 
category, 
sub_category,
ROUND(SUM(sales),2) AS sales_sub_category,
ROUND(SUM(SUM(sales)) OVER (PARTITION BY category), 2) AS sales_category, 
ROUND(SUM(SUM(sales)) OVER (), 2) AS sales_total, 
ROUND((SUM(sales) / SUM(SUM(sales)) OVER (PARTITION BY category)) * 100, 2) AS pct_in_category, 
ROUND((SUM(sales) / SUM(SUM(sales)) OVER ()) * 100, 2) AS pct_in_total
FROM records
GROUP BY category, sub_category
ORDER BY category, sub_category;

WITH SalesData AS (
    -- 각 서브 카테고리와 카테고리의 매출 계산
    SELECT
        category,
        sub_category,
        ROUND(SUM(sales), 2) AS sales_sub_category
    FROM records
    GROUP BY category, sub_category
),
CategorySales AS (
    -- 카테고리별 총 매출 계산
    SELECT
        category,
        ROUND(SUM(sales), 2) AS sales_category
    FROM records
    GROUP BY category
),
TotalSales AS (
    -- 전체 매출 계산
    SELECT
        ROUND(SUM(sales), 2) AS sales_total
    FROM records
)
SELECT
    sd.category,
    sd.sub_category,
    sd.sales_sub_category,
    cs.sales_category,
    ts.sales_total,
    ROUND((sd.sales_sub_category / cs.sales_category) * 100, 2) AS pct_in_category,
    ROUND((sd.sales_sub_category / ts.sales_total) * 100, 2) AS pct_in_total
FROM SalesData sd
JOIN CategorySales cs
    ON sd.category = cs.category
CROSS JOIN TotalSales ts
ORDER BY sd.category, sd.sub_category;