with
items as (
  select distinct
      ITEM_CODE
  from
      PURCHASE_HISTORY
),
all_pairs as (
  select
      a.ITEM_CODE as ITEM_A
    , b.ITEM_CODE as ITEM_B
  from
                items a
    cross join  items b
),
user_buy as (
  select
      i.ITEM_A
    , i.ITEM_B
    , u1.MEMBER_CODE as MEMBER_A
    , u2.MEMBER_CODE as MEMBER_B
  from
                    all_pairs i
    left outer join PURCHASE_HISTORY u1 on i.ITEM_A = u1.ITEM_CODE
    left outer join PURCHASE_HISTORY u2 on i.ITEM_B = u2.ITEM_CODE
),
user_info as (
  select
      count(distinct MEMBER_CODE) as TTL_USER
  from
      PURCHASE_HISTORY
),
pre_summary as (
  select
      ITEM_A
    , ITEM_B
    , count(distinct MEMBER_A) as USER_A
    , count(distinct MEMBER_B) as USER_B
    , count(distinct case
                        when MEMBER_A = MEMBER_B then MEMBER_A
                    end
        ) as USER_BOTH
    , TTL_USER
  from
      user_buy, user_info
  where
      ITEM_A <> ITEM_B
  group by
      ITEM_A
    , ITEM_B
)
select
    ITEM_A
  , ITEM_B
  , round(USER_BOTH * 100.0 / TTL_USER                , 5) as SUPPORT
  , round(USER_BOTH * 100.0 / USER_A                  , 5) as CONFIDENCE
  , round((USER_BOTH * 1.0 / USER_A ) * (USER_B * 1.0 / TTL_USER), 5) as LIFT
from
    pre_summary
order by
    LIFT       desc
  , SUPPORT    desc
  , CONFIDENCE desc
  , ITEM_A     desc