with TBL0 as (
    select count(distinct MEMBER_CODE) as MEMBER_CNT
    from PURCHASE_HISTORY
)
,TBL1 as (
    select MEMBER_CODE
        , TBL0.MEMBER_CNT
        , ITEM_CODE
        , count(1) OVER (Partition by ITEM_CODE) as ITEM_CNT
    from PURCHASE_HISTORY 
    cross join TBL0 
)
,TBL3 as (
    select
        T1.ITEM_CODE as ITEM_A , T2.ITEM_CODE as ITEM_B
        ,T1.ITEM_CNT as A_CNT , T2.ITEM_CNT as B_CNT
        , count(1) as AB_CNT
        , T1.MEMBER_CNT as MEMBER_CNT 
    
    from TBL1 as T1 inner join TBL1 as T2 on T1.MEMBER_CODE = T2.MEMBER_CODE
    where T1.ITEM_CODE <> T2.ITEM_CODE
    group by ITEM_A , ITEM_B , A_CNT , B_CNT , T1.MEMBER_CNT
)
select 
    ITEM_A , ITEM_B
    , round( 100.0 * AB_CNT / MEMBER_CNT , 5 ) as SUPPORT
    , round( 100.0 * AB_CNT / A_CNT , 5 ) as CONFIDENCE
    , round( ( 1.0 * AB_CNT / A_CNT ) / ( 1.0 * B_CNT / MEMBER_CNT) , 5 ) as LIFT
    from TBL3
    order by LIFT desc , SUPPORT desc , CONFIDENCE desc , ITEM_A desc