WITH TotalSales AS (
    SELECT SUM(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE 0 END) / 1000000 AS total_sales
    FROM CONVENIENCE
    WHERE SURVEY_YEAR = 2019
),
RankedPrefectures AS (
    SELECT *,
           DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE 0 END) DESC, 
                                       SUM(CASE WHEN KIND_CODE = '150' THEN TOTAL_VALUE ELSE 0 END) ASC,
                                       PF_CODE ASC) AS rank
    FROM CONVENIENCE
    WHERE SURVEY_YEAR = 2019
    GROUP BY PF_CODE
)
SELECT 
    rank AS NO,
    SUM(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE 0 END) / 1000000 AS TTL_SAL,
    ROUND((SUM(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE 0 END) / 1000000) * 100 / total_sales, 2) AS PER_SAL,
    ROUND(SUM(SUM(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE 0 END) / 1000000) OVER (ORDER BY rank) * 100 / total_sales, 2) AS CUM_SAL,
    CAST(SUM(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE 0 END) / SUM(CASE WHEN KIND_CODE = '150' THEN TOTAL_VALUE ELSE 0 END) AS INTEGER) AS AVG_SAL
FROM RankedPrefectures, TotalSales
WHERE rank <= 10
GROUP BY rank
ORDER BY NO ASC;