【問題1】在庫整理

DELETE 
FROM
    STOCK 
WHERE

    -- BETWEEN句で最終出荷日の範囲指定をおこなう
    LAST_DELIVERY_DATE BETWEEN '2023-01-01' AND '2023-01-31' 
    AND ACTUAL_AMT = 0;

【問題2】ドメイン名

SELECT
    MEMBER_CODE AS MEMBER
    , EMAIL AS EMAIL

    -- SUBSTRで、@マークの次の文字から、ドメイン拡張子の前の文字を切り出し
    , SUBSTR( 
        EMAIL

        -- INSTRで取得した@マークの位置に1をプラスし切り出しの先頭位置を取得
        , INSTR(EMAIL, '@') + 1

        -- INSTRでドメイン拡張子の位置から1をマイナスし、
        -- そこから@マークまでの位置をマイナスし切り出す文字数を取得
        , (INSTR(EMAIL, '.co.jp') - 1) - INSTR(EMAIL, '@')
    ) AS DOMAIN 
FROM
    MEMBER_MST 
ORDER BY
    DOMAIN DESC
    , MEMBER DESC;

【問題3】Zチャート

WITH SALES_YM AS ( 

    -- 月毎に売上金額を集計
    SELECT

       -- 売上日をYYYY-MMの形式に変換
        STRFTIME('%Y-%m', SALES_DATE) AS YM
        , SUM(SALES_AMT) AS MON_AMT 
    FROM
        SALES 
    WHERE

       -- 過去分の集計金額も必要なため1年前からのデータを集計対象とする
        YM >= '2022-01' 

       -- 黒伝のみを対象とするため、赤黒伝票番号がNULLの条件を記述
        AND UPDATED_NO IS NULL 
    GROUP BY
        YM
) 
, Z_CHART AS ( 

   -- 該当年月から過去11カ月の売上集計金額を取得
    SELECT
        YM AS YEAR_MONTH
        , MON_AMT AS MONTH_AMT

        -- SUM OVERで上記で求めた各月の売上集計金額を取得する
        , SUM(MON_AMT) OVER ( 
            ORDER BY

                -- 年月でソートし11行前までのデータを対象とする
                YM ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        ) AS YEAR_MOVE_AMT 
    FROM
        SALES_YM
) 
SELECT
    YEAR_MONTH
    , MONTH_AMT

    -- SUM OVERで該当月までの売上累計金額を取得する
    , SUM(MONTH_AMT) OVER (ORDER BY YEAR_MONTH) AS CUML_AMT
    , YEAR_MOVE_AMT 
FROM
    Z_CHART 
WHERE

   -- BETWEENで2023年のデータを絞り込む
    YEAR_MONTH BETWEEN '2023-01' AND '2023-12' 
ORDER BY
    YEAR_MONTH ASC;

【問題4】アソシエーション分析

WITH SUB1 AS ( 
    -- 商品毎の購買顧客件数を取得
    SELECT
        PH.MEMBER_CODE
        , MC.MEMBER_CNT
        , PH.ITEM_CODE

        -- COUNT OVERで商品毎の購買顧客件数をカウントする
        , 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 ( 

    -- 商品A、Bが同じ組合せの購買顧客数を取得
    SELECT
        S1.ITEM_CODE AS ITEM_A
        , S2.ITEM_CODE AS ITEM_B
        , S1.ITEM_CNT AS A_CNT -- 商品Aの購買顧客数
        , S2.ITEM_CNT AS B_CNT -- 商品Bの購買顧客数

        -- 商品A、Bが同じ組合せの購買顧客数を取得
        , COUNT(1) AS AB_CNT
        , S1.MEMBER_CNT AS M_CNT 
    FROM
        SUB1 AS S1 

        -- 上記SUB1を会員コードで自己結合する
        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で小数点第6位を四捨五入する
    , 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;