select
    t1.ITEM_CODE as CODE
    ,t3.ITEM_NAME as NAME
    ,SUM(t1.UNITPRICE * t1.SALES_QTY) as SAL_AMT
    ,SUM(SUM(t1.UNITPRICE * t1.SALES_QTY)) OVER(order by SUM(t1.UNITPRICE * t1.SALES_QTY) DESC) as CML_AMT
    ,ROUND(CAST(SUM(t1.UNITPRICE * t1.SALES_QTY) as REAL) / CAST(SUM(SUM(t1.UNITPRICE * t1.SALES_QTY)) OVER() as REAL) * 100, 1) || '%' as SAL_COMP
    ,ROUND(CAST(SUM(SUM(t1.UNITPRICE * t1.SALES_QTY)) OVER(order by SUM(t1.UNITPRICE * t1.SALES_QTY) DESC) as REAL) / CAST(SUM(SUM(t1.UNITPRICE * t1.SALES_QTY)) OVER() as REAL) * 100, 1) || '%' as TTL_COMP
    ,CASE
        WHEN ROUND(CAST(SUM(SUM(t1.UNITPRICE * t1.SALES_QTY)) OVER(order by SUM(t1.UNITPRICE * t1.SALES_QTY) DESC) as REAL) / CAST(SUM(SUM(t1.UNITPRICE * t1.SALES_QTY)) OVER() as REAL) * 100, 1) <= 40 THEN 'A'
        WHEN ROUND(CAST(SUM(SUM(t1.UNITPRICE * t1.SALES_QTY)) OVER(order by SUM(t1.UNITPRICE * t1.SALES_QTY) DESC) as REAL) / CAST(SUM(SUM(t1.UNITPRICE * t1.SALES_QTY)) OVER() as REAL) * 100, 1) <= 80 THEN 'B'
        ELSE 'C'
    END as RANK
from
    SALES_DTL t1
        left outer join
    SALES t2
        on t1.SALES_NO = t2.SALES_NO
        left outer join
    ITEM t3
        on t1.ITEM_CODE = t3.ITEM_CODE

where
    t2.SALES_DATE between '2023-06-01' and '2023-06-31'
group by
    t1.ITEM_CODE
order by
    SAL_AMT DESC
    ,t1.ITEM_CODE DESC
;