ソースコード
WITH TOTAL_SAL AS(
 SELECT
  SUM(TOTAL_VALUE) AS TOTAL_AMT
 FROM
  CONVENIENCE
 WHERE
  SURVEY_YEAR = 2019
  AND KIND_CODE = '100'
),
DECILE_GROUP AS(
 SELECT 
  C1.PF_CODE,
  C1.TOTAL_VALUE AS TOTAL_SAL,
  C2.TOTAL_VALUE AS TOTAL_SHOP,
  NTILE(10) OVER (ORDER BY C1.TOTAL_VALUE DESC, C2.TOTAL_VALUE ASC, C1.PF_CODE ASC) AS DC_NO
 FROM
  CONVENIENCE AS C1
  INNER JOIN CONVENIENCE AS C2
   ON C1.PF_CODE = C2.PF_CODE
      AND C2.SURVEY_YEAR = 2019
      AND C2.KIND_CODE = '150'
 WHERE
  C1.SURVEY_YEAR = 2019
  AND C1.KIND_CODe = '100'
),
DC_SUMMARY AS (
 SELECT
  DC_NO,
  SUM(TOTAL_SAL) AS DC_SAL,
  SUM(TOTAL_SHOP) AS DC_SHOP
 FROM
  DECILE_GROUP
 GROUP BY
  DC_NO
)

SELECT
 DS.DC_NO AS NO,
 DS.DC_SAL AS TTL_SAL,
 ROUND((CAST(DS.DC_SAL AS REAL) / TS.TOTAL_AMT * 100),1) AS PER_SAL,
 ROUND((CAST(SUM(DS.DC_SAL) OVER (ORDER BY DS.DC_NO ASC) AS REAL) / TS.TOTAL_AMT * 100) ,1) AS CUM_SAL,
 CAST((CAST(DS.DC_SAL AS REAL) / DS.DC_SHOP) AS INT) AS AVG_SAL

FROM
 DC_SUMMARY AS DS
 CROSS JOIN TOTAL_SAL AS TS
 
ORDER BY 
 DS.DC_NO ASC;
 
 
 
提出情報
提出日時2024/08/28 14:37:52
コンテスト第9回 SQLコンテスト
問題販売額分析
受験者tatataka
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量87 MB
メッセージ
テストケース(通過数/総数)
3/3
状態
メモリ使用量
データパターン1
AC
87 MB
データパターン2
AC
85 MB
データパターン3
AC
83 MB