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
)
, sumtbl as (
    SELECT ITEM_CODE, IFNULL(SUMS, 0) AS SUMS
    FROM ITEM
    LEFT JOIN sumtbl_ USING (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%', ROUND(HI * 100, 1)) AS SAL_COMP,
PRINTF('%.1f%', ROUND(CAST ( RUI AS REAL ) / (SELECT SUMS FROM alltbl) * 100, 1))  AS TTL_COMP,
CASE
    WHEN CAST ( RUI AS REAL ) / (SELECT SUMS FROM alltbl) <= 0.40 THEN 'A'
    WHEN CAST ( RUI AS REAL ) / (SELECT SUMS FROM alltbl) <= 0.80 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)
ORDER BY SUMS DESC, ITEM_CODE DESC