with total as (
    select
        count(distinct session_id) as total
    from purchase_history
)
,T as (
    select
        A.item_code as item_a
        ,B.item_code as item_b
        ,count(*) as count_sup
    from purchase_history as A
    left outer join purchase_history as B
    on A.session_id = B.session_id
    and A.item_code <> B.item_code
    group by A.item_code,B.item_code
)
,prod_count as(
    select
        item_code
        ,count(*) as count_prod
    from purchase_history 
    group by item_code
)
select
    item_a as ITEM_A
    ,item_b as ITEM_B
    ,round(count_sup*100.0/total,5) as SUPPORT
    ,round(count_sup*100.0/A.count_prod,5) as CONFIDENCE
    ,round((1.0*count_sup/A.count_prod)/(1.0*B.count_prod/total),5)  as LIFT
from T
inner join prod_count as A
on A.item_code = T.item_a
inner join prod_count as B
on B.item_code = T.item_b
cross join total
order by LIFT desc,SUPPORT desc,CONFIDENCE desc,ITEM_A desc