WITH RECURSIVE item_pairs AS (
  SELECT
    a.ITEM_CODE AS ITEM_A,
    b.ITEM_CODE AS ITEM_B
  FROM
    PURCHASE_HISTORY a
  JOIN
    PURCHASE_HISTORY b ON a.SESSION_ID = b.SESSION_ID AND a.ITEM_CODE != b.ITEM_CODE
),
aggregated_data AS (
  SELECT
    ITEM_A,
    ITEM_B,
    COUNT(*) AS PAIR_COUNT,
    (SELECT COUNT(DISTINCT SESSION_ID) FROM PURCHASE_HISTORY) AS TOTAL_SESSIONS,
    (SELECT COUNT(*) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_A) AS ITEM_A_COUNT,
    (SELECT COUNT(*) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_B) AS ITEM_B_COUNT
  FROM
    item_pairs
  GROUP BY
    ITEM_A, ITEM_B
)
SELECT
  ITEM_A,
  ITEM_B,
  ROUND((PAIR_COUNT * 100.0 / TOTAL_SESSIONS), 6) AS SUPPORT,
  ROUND((PAIR_COUNT * 100.0 / ITEM_A_COUNT), 6) AS CONFIDENCE,
  ROUND((PAIR_COUNT / (ITEM_A_COUNT * 1.0)) / (ITEM_B_COUNT / (TOTAL_SESSIONS*1.0)), 6) AS LIFT
FROM
  aggregated_data
ORDER BY
  LIFT DESC,
  SUPPORT DESC,
  CONFIDENCE DESC,
  ITEM_A DESC;