WITH PairCounts AS (
    SELECT
        PH1.ITEM_CODE AS ITEM_A,
        PH2.ITEM_CODE AS ITEM_B,
        COUNT(DISTINCT PH1.MEMBER_CODE) AS PAIR_COUNT,
        COUNT(DISTINCT PH1.SESSION_ID) AS TOTAL_TRANSACTIONS
    FROM
        PURCHASE_HISTORY PH1
    JOIN PURCHASE_HISTORY PH2 ON PH1.SESSION_ID = PH2.SESSION_ID AND PH1.ITEM_CODE < PH2.ITEM_CODE
    GROUP BY
        ITEM_A, ITEM_B
),
ItemCounts AS (
    SELECT
        ITEM_CODE,
        COUNT(DISTINCT MEMBER_CODE) AS ITEM_COUNT
    FROM
        PURCHASE_HISTORY
    GROUP BY
        ITEM_CODE
),
TotalCustomers AS (
    SELECT
        COUNT(DISTINCT MEMBER_CODE) AS TOTAL_CUSTOMERS
    FROM
        PURCHASE_HISTORY
),
AssociationMetrics AS (
    SELECT
        PC.ITEM_A,
        PC.ITEM_B,
        ROUND((CAST(PC.PAIR_COUNT AS REAL) / TC.TOTAL_CUSTOMERS) * 100, 6) AS SUPPORT,
        ROUND((CAST(PC.PAIR_COUNT AS REAL) / IC_A.ITEM_COUNT) * 100, 6) AS CONFIDENCE,
        ROUND(((CAST(PC.PAIR_COUNT AS REAL) / IC_A.ITEM_COUNT) / (CAST(IC_B.ITEM_COUNT AS REAL) / TC.TOTAL_CUSTOMERS)), 6) AS LIFT
    FROM
        PairCounts PC
    JOIN ItemCounts IC_A ON PC.ITEM_A = IC_A.ITEM_CODE
    JOIN ItemCounts IC_B ON PC.ITEM_B = IC_B.ITEM_CODE,
        TotalCustomers TC
)

SELECT
    ITEM_A,
    ITEM_B,
    SUPPORT,
    CONFIDENCE,
    LIFT
FROM
    AssociationMetrics
ORDER BY
    LIFT DESC,
    SUPPORT DESC,
    CONFIDENCE DESC,
    ITEM_A DESC;