select CODE, NAME, SAL_AMT, CML_AMT,--  SAL_COMP_0,TTL_COMP_0,
(case 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, 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_1 * 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, ITEM_CODE desc) as CML_AMT, sum(SAL_AMT) over(order by SAL_AMT desc) as CML_AMT_1, 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