--調査年(SURVEY_YEAR)が2019年
--KIND_CODE'100' が「販売額」、'150' が「店舗数」
WITH
A AS(
SELECT *,
SUM(CASE KIND_CODE 
WHEN 100 THEN TOTAL_VALUE
ELSE 0
END)AS SAL,
SUM(CASE KIND_CODE 
WHEN 150 THEN TOTAL_VALUE
ELSE 0
END)AS SP
FROM CONVENIENCE
WHERE SURVEY_YEAR = 2019
GROUP BY PF_CODE
),

--販売額>店舗数が少ない順>都道府県コードの昇順
--番号を振る

B AS(
SELECT SAL,SP,PF_CODE,
ROW_NUMBER() OVER(ORDER BY SAL DESC,SP ASC,PF_CODE ASC) NUM
FROM A
GROUP BY PF_CODE
),

--販売額が多い順に3都道府県毎に10グループに分割
C AS(
SELECT SAL,SP,PF_CODE,NUM,
(CASE
WHEN NUM IN(1,2,3) THEN 1
WHEN NUM IN(4,5,6) THEN 2
WHEN NUM IN(7,8,9) THEN 3
WHEN NUM IN(10,11,12) THEN 4
WHEN NUM IN(13,14,15) THEN 5
WHEN NUM IN(16,17,18) THEN 6
WHEN NUM IN(19,20,21) THEN 7
WHEN NUM IN(22,23,24) THEN 8
WHEN NUM IN(25,26,27) THEN 9
WHEN NUM IN(28,29,30) THEN 10
ELSE 0
END
) AS NO
FROM B
),


--グループの販売額合計
D AS(
SELECT SUM(SAL) AS SUMSAL
FROM A
),

--グループの販売額合計 ÷ 2019年の全体の販売額合計、小数第2位四捨五入
--累計販売額比率、小数第2位四捨五入
--sum(Value) over(order by Date) as Accumulated
--1店舗当たりの平均販売額、小数点以下切捨

E AS( 
SELECT
NO,SUMSAL,
SUM(SAL) AS TTL_SAL,
ROUND((SUM(SAL)*1.00 / SUMSAL*1.00 *100), 1) AS PER_SAL,
SUM(SAL) / SUM(SP) AS AVG_SAL
FROM C
FULL OUTER JOIN D
GROUP BY NO),

F AS(
SELECT
*,
SUM(TTL_SAL) OVER(ORDER BY NO) AS RUI
FROM E),

G AS(
SELECT
NO,TTL_SAL,PER_SAL,
ROUND(RUI*1.00 / SUMSAL*1.00*100,1)  AS CUM_SAL,
AVG_SAL
FROM F)


SELECT *
FROM G;