WITH PairwiseCombinations AS (
    SELECT
        a.SESSION_ID,
        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
),
SupportCount AS (
    SELECT
        ITEM_A,
        ITEM_B,
        COUNT(*) AS SupportCount
    FROM
        PairwiseCombinations
    GROUP BY
        ITEM_A, ITEM_B
),
TotalSessions AS (
    SELECT
        COUNT(DISTINCT SESSION_ID) AS TotalSessions
    FROM
        PURCHASE_HISTORY
),
ItemAPurchaseCount AS (
    SELECT
        ITEM_CODE,
        COUNT(DISTINCT SESSION_ID) AS PurchaseCount
    FROM
        PURCHASE_HISTORY
    GROUP BY
        ITEM_CODE
),
ItemBPurchaseCount AS (
    SELECT
        ITEM_CODE,
        COUNT(DISTINCT SESSION_ID) AS PurchaseCount
    FROM
        PURCHASE_HISTORY
    GROUP BY
        ITEM_CODE
)
SELECT
    s.ITEM_A,
    s.ITEM_B,
    ROUND((CAST(s.SupportCount AS FLOAT) / t.TotalSessions) * 100, 6) AS SUPPORT,
    ROUND((CAST(s.SupportCount AS FLOAT) / a.PurchaseCount) * 100, 6) AS CONFIDENCE,
    ROUND(((CAST(s.SupportCount AS FLOAT) / a.PurchaseCount) / (CAST(b.PurchaseCount AS FLOAT) / t.TotalSessions)), 6) AS LIFT
FROM
    SupportCount s
JOIN
    TotalSessions t ON 1=1
JOIN
    ItemAPurchaseCount a ON s.ITEM_A = a.ITEM_CODE
JOIN
    ItemBPurchaseCount b ON s.ITEM_B = b.ITEM_CODE
ORDER BY
    LIFT DESC,
    SUPPORT DESC,
    CONFIDENCE DESC,
    ITEM_A DESC;