with combi as (
    select
        t1.ITEM_CODE as ITEM_A
        , t2.ITEM_CODE as ITEM_B
        , count(t1.SESSION_ID) as cnt
    from 
        PURCHASE_HISTORY t1
        left outer join (
            select
                SESSION_ID
                , ITEM_CODE
            from 
                PURCHASE_HISTORY
        ) t2
        on
            t1.SESSION_ID = t2.SESSION_ID
            and
            t1.ITEM_CODE <> t2.ITEM_CODE
    group by
        t1.ITEM_CODE
        , t2.ITEM_CODE
    having
        t1.ITEM_CODE is not null
        and
        t2.ITEM_CODE is not null
        
)
, customer_cnt as (
    select count(distinct MEMBER_CODE) as cnt_cust
    from PURCHASE_HISTORY
)
, item_cnt as(
    select
        ITEM_CODE
        , count(distinct MEMBER_CODE) as cnt_ITEM
    from PURCHASE_HISTORY
    group by ITEM_CODE
)
select
    c.ITEM_A
    , c.ITEM_B
    , round(100.0 * c.cnt / cc.cnt_cust, 5) as SUPPORT
    , round(100.0 * c.cnt / ba.cnt_ITEM, 5) as CONFIDENCE
    , round((1.0 * c.cnt / ba.cnt_ITEM) / (1.0 * bb.cnt_ITEM / cc.cnt_cust), 5) as LIFT
from
    combi as c
    cross join
        customer_cnt as cc
    left outer join
        item_cnt as ba
        on c.ITEM_A = ba.ITEM_CODE
    left outer join
        item_cnt as bb
        on c.ITEM_B = bb.ITEM_CODE
order by
    LIFT desc
    , SUPPORT desc
    , CONFIDENCE desc
    , c.ITEM_A desc
;