WITH 
customers AS (
    SELECT 
        DISTINCT
            MEMBER_CODE
    FROM
        PURCHASE_HISTORY
),

item_stats AS (
    SELECT 
        ITEM_CODE,
        COUNT(DISTINCT MEMBER_CODE) AS uu
    FROM
        PURCHASE_HISTORY
),

pairs AS (
    SELECT 
        item_a.ITEM_CODE AS ITEM_A,
        item_b.ITEM_CODE AS ITEM_B,
        COUNT(DISTINCT item_a.MEMBER_CODE) AS uu
    FROM
        PURCHASE_HISTORY AS item_a
    INNER JOIN 
        PURCHASE_HISTORY AS item_b
    ON
        item_a.MEMBER_CODE = item_b.MEMBER_CODE
        AND item_a.ITEM_CODE != item_b.ITEM_CODE
    GROUP BY 
        ITEM_A,
        ITEM_B
)

-- 支持度 ＝ 商品Aと商品Bを購買した顧客数 ÷ 全顧客数 × 100
-- 信頼度 ＝ 商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数 × 100
-- リフト値 ＝ (商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数) ÷ (商品Bを購買した顧客数÷全顧客数)
SELECT 
    ITEM_A,
    ITEM_B,
    -- uu,
    -- (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY) AS total_uu,
    -- (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_A) AS item_a_uu,
    -- (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_B) AS item_b_uu,
    ROUND(CAST(uu AS FLOAT) / (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY) * 100, 5) AS SUPPORT,
    ROUND(CAST(uu AS FLOAT) / (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_A) * 100, 5) AS CONFIDENCE,
    ROUND(CAST(uu AS FLOAT) / (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_A) 
        / (CAST((SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_B) AS FLOAT) / (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY)) * 100, 5) AS LIFT
FROM
    pairs
ORDER BY 
    LIFT DESC, -- 算出したリフト値の降順
    SUPPORT DESC, -- 算出した支持度の降順
    CONFIDENCE DESC, -- 算出した信頼度値の降順
    ITEM_A DESC-- 商品Aの商品コードの降順