WITH SUB1 AS(
 SELECT
  PH.MEMBER_CODE,
  MC.MEMBER_CNT,
  PH.ITEM_CODE,
  COUNT(1) OVER (PARTITION BY PH.ITEM_CODE) AS ITEM_CNT
  FROM
   PURCHASE_HISTORY AS PH
   
   CROSS JOIN (
    SELECT
     COUNT(DISTINCT MEMBER_CODE) AS MEMBER_CNT
    FROM
     PURCHASE_HISTORY) AS MC
),

SUB2 AS(
 SELECT
  S1.ITEM_CODE AS ITEM_A,
  S2.ITEM_CODE AS ITEM_B,
  S1.ITEM_CNT AS A_CNT,
  S2.ITEM_CNT AS B_CNT,
  COUNT(1) AS AB_CNT,
  S1.MEMBER_CNT AS M_CNT
 FROM
  SUB1 AS S1
   INNER JOIN SUB1 AS S2
    ON S1.MEMBER_CODE = S2.MEMBER_CODE
 WHERE
  S1.ITEM_CODE <> S2.ITEM_CODE
 GROUP BY
  ITEM_A,
  ITEM_B,
  A_CNT,
  B_CNT,
  M_CNT
)

SELECT
 ITEM_A,
 ITEM_B,
 ROUND(100.0 * AB_CNT / M_CNT,5) AS SUPPORT,
 ROUND(100.0 * AB_CNT / A_CNT,5) AS CONFIDENCE,
 ROUND((1.0 * AB_CNT / A_CNT ) / (1.0 * B_CNT / M_CNT) ,5) AS LIFT
 
FROM
 SUB2
ORDER BY
 LIFT DESC,
 SUPPORT DESC,
 CONFIDENCE DESC,
 ITEM_A DESC;