with total as(
    select
        count(distinct MEMBER_CODE) as MEMBER_CNT
    from PURCHASE_HISTORY
), temp1 as (
    select
        ITEM_CODE
        , count(distinct MEMBER_CODE) as MEMBER_CNT
    from PURCHASE_HISTORY
    group by ITEM_CODE
), temp2 as (
    select
        pa.ITEM_CODE as ITEM_A
        , pb.ITEM_CODE as ITEM_B
        , count(distinct pa.MEMBER_CODE) as MEMBER_CNT
    from PURCHASE_HISTORY as pa, PURCHASE_HISTORY as pb
    where pa.ITEM_CODE<>pb.ITEM_CODE and pa.MEMBER_CODE=pb.MEMBER_CODE
    group by ITEM_A, ITEM_B
)

select
    tab.ITEM_A
    , tab.ITEM_B
    , round((cast(tab.MEMBER_CNT as real) / t.MEMBER_CNT)*100, 5) as SUPPORT
    , round((cast(tab.MEMBER_CNT as real) / ta.MEMBER_CNT)*100, 5) as CONFIDENCE
    ,round(((cast(tab.MEMBER_CNT as real) / ta.MEMBER_CNT)) / (cast(tb.MEMBER_CNT as real) / t.MEMBER_CNT), 5) as LIFT
from temp2 as tab, total as t
inner join temp1 as ta on tab.ITEM_A=ta.ITEM_CODE
inner join temp1 as tb on tab.ITEM_B=tb.ITEM_CODE
order by
    LIFT desc
    , SUPPORT desc
    , CONFIDENCE desc
    , ITEM_A desc