-- with
-- item_codes as (select distinct item_code from purchase_history)
-- , item_combinations as (select a.item_code as ITEM_A, b.item_code as ITEM_B from item_codes as a full join item_codes as b where ITEM_A != ITEM_B)
-- , order_num as (select count(distinct session_id) as num from purchase_history)
-- , support as (select count(distinct session_id) as session from item_combinations)
-- select * from item_combinations, order_num
-- ;


with items as (
  select item_code, member_code, count(1) over (partition by item_code) as item_count
  from purchase_history
)
, item_combinations as (
  select a.item_code as ITEM_A, b.item_code as ITEM_B, a.item_count as A_CNT, b.item_count as B_CNT , count(1) as AB_CNT
  from items as a join items as b on a.member_code = b.member_code
  where ITEM_A != ITEM_B
  group by ITEM_A, ITEM_B, A_CNT, B_CNT
)
, order_num as (select count(distinct session_id) as num from purchase_history)
select 
  item_combinations.ITEM_A
  , item_combinations.ITEM_B
  , ROUND(100.0 * item_combinations.AB_CNT / order_num.num, 5) as SUPPORT
  , ROUND(100.0 * item_combinations.AB_CNT / item_combinations.A_CNT, 5) as CONFIDENCE
  , ROUND((1.0 * item_combinations.AB_CNT / item_combinations.A_CNT) / (1.0 * item_combinations.B_CNT / order_num.num), 5) as LIFT
from item_combinations, order_num
order by LIFT desc, SUPPORT desc, CONFIDENCE desc, ITEM_A desc
;