--売上データ(SALES):
--└売上日(SALES_DATE)の期間は2023年6月1日から30日
WITH A AS(
SELECT SALES_NO
FROM SALES
WHERE SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'),
--売上明細データ(SALES_DTL)
--└売上金額（UNITPRICE × SALES_QTY）
--アイテム別で集計
B AS(
SELECT S.ITEM_CODE AS CODE,I.ITEM_NAME AS NAME,
SUM(S.UNITPRICE * S.SALES_QTY) AS SAL_AMT
FROM SALES_DTL AS S
JOIN A
ON S.SALES_NO = A.SALES_NO
JOIN ITEM AS I
ON S.ITEM_CODE = I.ITEM_CODE
GROUP BY S.ITEM_CODE
ORDER BY SUM(S.UNITPRICE * S.SALES_QTY) DESC,
S.ITEM_CODE DESC),
C AS(
SELECT *,
SUM(SAL_AMT) OVER() AS 'SUM1',
SAL_AMT*1.00 / SUM(SAL_AMT) OVER() AS 'SUM2',
RANK() OVER(ORDER BY SAL_AMT DESC,CODE DESC) AS NUM
FROM B),
D AS(
SELECT
C1.CODE,C1.NAME,C1.SAL_AMT,
C1.SUM1,C1.SUM2,C1.NUM,
--集計した売上金額が同じ場合は、それぞれの金額の合計を累積集計の売上金額として計算する。
SUM(C1.SAL_AMT) OVER(ORDER BY C1.SAL_AMT DESC) AS CML_AMT,
SUM(C1.SAL_AMT) OVER(ORDER BY C1.SAL_AMT DESC) *1.00 / C1.SUM1 AS TTL_COMP
FROM C AS C1
INNER JOIN C AS C2
ON C1.NUM >= C2.NUM
GROUP BY C1.NUM,C1.SAL_AMT
ORDER BY C1.NUM),
--40%以下をランクA、40%より大きく80%以下をランクB、
--それ以外をランクC
E AS(
SELECT *,
(CASE
WHEN TTL_COMP <= 0.4 THEN 'A'
WHEN TTL_COMP > 0.4 AND TTL_COMP <= 0.8 THEN 'B'
ELSE 'C' END) AS RANK
FROM D),
F AS(
SELECT CODE,NAME,CML_AMT,SAL_AMT,
ROUND(SUM2 *100, 1) || "%" AS SAL_COMP,
ROUND(TTL_COMP *100, 1) || "%" AS TTL_COMP,
RANK
FROM E
ORDER BY SAL_AMT DESC,CODE DESC)

SELECT *
FROM F;