コンテストの制限時間が終了しました。
以降も提出を行うことができますが、順位集計には反映されません。
以降も提出を行うことができますが、順位集計には反映されません。
ソースコード
select CODE, NAME, SAL_AMT, CML_AMT,-- SAL_COMP_0,TTL_COMP_0,
(case when substr(SAL_COMP_0, 1, 2) = "00" then (substr(SAL_COMP_0, 3, 1) || "." || substr(SAL_COMP_0, -1, 1) || "%") when substr(SAL_COMP_0, 1, 1) = "0" then (substr(SAL_COMP_0, 2, 2) || "." || substr(SAL_COMP_0, -1, 1) || "%") else (substr(SAL_COMP_0, 1, 3) || "." || substr(SAL_COMP_0, -1, 1) || "%") end) as SAL_COMP,
(case when substr(TTL_COMP_0, 1, 2) = "00" then (substr(TTL_COMP_0, 3, 1) || "." || substr(TTL_COMP_0, -1, 1) || "%") when substr(TTL_COMP_0, 1, 1) = "0" then (substr(TTL_COMP_0, 2, 2) || "." || substr(TTL_COMP_0, -1, 1) || "%") else (substr(TTL_COMP_0, 1, 3) || "." || substr(TTL_COMP_0, -1, 1) || "%") end) as TTL_COMP,
(case when cast(TTL_COMP_0 as int) <= 400 then "A" when cast(TTL_COMP_0 as int) <= 800 then "B" else "C" end) as RANK
-- "A" as rank
from (
select ITEM_CODE as CODE, ITEM_NAME as NAME, SAL_AMT, CML_AMT, substr("0000"||cast(round(cast(SAL_AMT * 1000 as float) / sum_SAL_AMT) as int), -4, 4) as SAL_COMP_0,
substr("0000"||cast(round(cast(CML_AMT * 1000 as float) / sum_SAL_AMT) as int), -4, 4) as TTL_COMP_0
from (
select ITEM_CODE, ITEM_NAME, SAL_AMT, sum(SAL_AMT) over(order by SAL_AMT desc) as CML_AMT, sum_SAL_AMT
from (
select ITEM_CODE, ITEM_NAME, SAL_AMT, sum(SAL_AMT) over() as sum_SAL_AMT
from (
select ITEM_CODE, ITEM_NAME, sum(price) as SAL_AMT
from (
select ITEM_CODE, ITEM_NAME, DELIVERED_QTY * UNITPRICE as price
from SALES_DTL
left join ITEM using (ITEM_CODE)
left join SALES using (SALES_NO)
where "2023-06-01" <= SALES_DATE and SALES_DATE <= "2023-06-30"
)
group by ITEM_CODE
order by SAL_AMT desc
)
)
)
)
order by SAL_AMT desc, CODE desc
-- select * from SALES_DTL
-- left join SALES using (SALES_NO)
-- left join
提出情報
提出日時 | 2023/06/19 07:27:48 |
コンテスト | 第7回 SQLコンテスト |
問題 | ABC分析 |
受験者 | Tomii9273 |
状態 (詳細) | WA (Wrong Answer: 誤答) |
メモリ使用量 | 79 MB |
メッセージ
テストケース(通過数/総数)
1/3
状態
メモリ使用量
データパターン1
AC
79 MB
データパターン2
WA
77 MB
データパターン3
WA
79 MB