with sumtbl as (
    SELECT ITEM_CODE, SUM(UNITPRICE * DELIVERED_QTY) AS SUMS
    FROM SALES_DTL
    LEFT JOIN SALES USING (SALES_NO)
    WHERE SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    group by ITEM_CODE
)
, alltbl as (
    SELECT SUM(UNITPRICE * DELIVERED_QTY) AS SUMS
    FROM SALES_DTL
    LEFT JOIN SALES USING (SALES_NO)
    WHERE SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
)
, hitbl as (
    SELECT ITEM_CODE, CAST( SUMS AS REAL ) / (SELECT SUMS FROM alltbl) as HI
    FROM sumtbl
)
, sumtbl2 as (
    SELECT X.ITEM_CODE, sum(Y.SUMS) AS RUI
    from sumtbl AS X
    LEFT JOIN sumtbl AS Y ON Y.SUMS >= X.SUMS
    group by X.ITEM_CODE
)
SELECT
ITEM_CODE AS CODE,
ITEM_NAME AS NAME,
SUMS AS SAL_AMT,
RUI AS CML_AMT,
PRINTF('%.1f%', HI * 100) AS SAL_COMP,
PRINTF('%.1f%', ROUND(CAST ( RUI AS REAL ) / (SELECT SUMS FROM alltbl) * 100, 1))  AS TTL_COMP,
CASE
    WHEN ROUND(CAST ( RUI AS REAL ) / (SELECT SUMS FROM alltbl) * 100, 1) <= 40.00 THEN 'A'
    WHEN ROUND(CAST ( RUI AS REAL ) / (SELECT SUMS FROM alltbl) * 100, 1) <= 80.00 THEN 'B'
    ELSE 'C'
END AS RANK
FROM ITEM
LEFT JOIN sumtbl USING (ITEM_CODE)
LEFT JOIN hitbl USING (ITEM_CODE)
LEFT JOIN sumtbl2 USING (ITEM_CODE)
WHERE SUMS IS NOT NULL
ORDER BY SUMS DESC, ITEM_CODE DESC