コンテストの制限時間が終了しました。
以降も提出を行うことができますが、順位集計には反映されません。
以降も提出を行うことができますが、順位集計には反映されません。
ソースコード
SELECT MONTH.YEAR_MONTH,
MONTH.MONTH_AMT,
MONTH.CUML_AMT,
BEFORE.SALES_AMT + MONTH.MONTH_AMT AS YEAR_MOVE_AMT
FROM (SELECT BASE.MONTH AS YEAR_MONTH,
BASE.TOTAL_SALES AS MONTH_AMT,
SUM(CUML.TOTAL_SALES) AS CUML_AMT
FROM (SELECT SUBSTR(SALES_DATE,1,7) AS MONTH,
SUM(SALES_AMT) AS TOTAL_SALES
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2023-01' AND '2023-12'
GROUP BY SUBSTR(SALES_DATE,1,7)
) BASE INNER JOIN (SELECT SUBSTR(SALES_DATE,1,7) AS MONTH,
SUM(SALES_AMT) AS TOTAL_SALES
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2023-01' AND '2023-12'
GROUP BY SUBSTR(SALES_DATE,1,7)
) CUML
ON BASE.MONTH >= CUML.MONTH
GROUP BY BASE.MONTH,BASE.TOTAL_SALES
) MONTH INNER JOIN (SELECT '2023-01' AS YEAR_MONTH,
SUM(SALES_AMT) AS SALES_AMT
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-02' AND '2022-12'
UNION
SELECT '2023-02' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-03' AND '2023-01'
UNION
SELECT '2023-03' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-04' AND '2023-02'
UNION
SELECT '2023-04' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-05' AND '2023-03'
UNION
SELECT '2023-05' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-06' AND '2023-04'
UNION
SELECT '2023-06' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-07' AND '2023-05'
UNION
SELECT '2023-07' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-08' AND '2023-06'
UNION
SELECT '2023-08' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-09' AND '2023-07'
UNION
SELECT '2023-09' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-10' AND '2023-08'
UNION
SELECT '2023-10' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-11' AND '2023-09'
UNION
SELECT '2023-11' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2022-12' AND '2023-10'
UNION
SELECT '2023-12' AS YEAR_MONTH,
SUM(SALES_AMT)
FROM SALES
WHERE UPDATED_NO IS NULL
AND SUBSTR(SALES_DATE,1,7) BETWEEN '2023-01' AND '2023-11'
) BEFORE
ON MONTH.YEAR_MONTH = BEFORE.YEAR_MONTH
ORDER BY MONTH.YEAR_MONTH
提出情報
提出日時 | 2024/02/16 17:26:55 |
コンテスト | 第11回 SQLコンテスト |
問題 | Zチャート |
受験者 | yng |
状態 (詳細) | AC (Accepted: 正答) |
メモリ使用量 | 89 MB |
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
89 MB
データパターン2
AC
85 MB