with members_by_item as (
    select item_code, count(distinct member_code) as count_by_item
    from purchase_history
    group by 1
), 
all_members as (
    select count(distinct member_code) as count_members
    from purchase_history
)
select 
    a.item_code as ITEM_A
    , b.item_code as ITEM_B
    , round(
        count(distinct a.member_code) * cast(100 as float)
        / c.count_members
        , 5) as SUPPORT
    , round(
        count(distinct a.member_code) * cast(100 as float)
        / a_count.count_by_item
        , 5) as CONFIDENCE
    , round(
        count(distinct a.member_code) * c.count_members * cast(1 as float)
        / a_count.count_by_item / b_count.count_by_item
        , 5) as LIFT
from purchase_history as a
    inner join purchase_history as b
        on a.session_id = b.session_id
            and a.item_code <> b.item_code
    left outer join members_by_item as a_count
        on a.item_code = a_count.item_code
    left outer join members_by_item as b_count
        on b.item_code = b_count.item_code
    cross join all_members as c
group by 1,2
order by 
    LIFT DESC
    , SUPPORT DESC
    , CONFIDENCE DESC  
    , ITEM_A DESC