【問題1】文字変換

SELECT
    EMP_CODE AS CODE
    , UPPER(EMP_ENG_NAME) AS ENG_NAME 
FROM
    EMP 
ORDER BY
    EMP_CODE DESC;

【問題2】新規ログイン数

SELECT
    FIRST_LOGIN_DATE AS FIRST_LOGIN
    , COUNT(MEMBER_CODE) AS MEMBER_CNT 
FROM
    ( 

        -- 会員毎に初めにログインした日を取得
        SELECT
            MEMBER_CODE

              -- DATE関数で日付のみに編集
            , MIN(DATE (LOGIN_DATETIME)) AS FIRST_LOGIN_DATE 
        FROM
            ACCESS_LOG 
        GROUP BY
            MEMBER_CODE
    ) AS SUB
WHERE
    FIRST_LOGIN_DATE BETWEEN '2023-08-01' AND '2023-08-31' 
GROUP BY
    FIRST_LOGIN_DATE 
ORDER BY
    FIRST_LOGIN_DATE DESC;

【問題3】クーポン配布対象

SELECT
    SUB.ORDER_DATETIME AS ORDER_DATE
    , SUB.MEMBER_CODE AS CODE
    , CASE MM.OPTOUT_TYPE 
        WHEN 0 THEN '可' 
        WHEN 1 THEN '不可' 
        ELSE '不明' 
        END AS OPTOUT 
FROM
    ( 

        -- 会員毎に7月に初めて受注した日付を取得
        SELECT
            MEMBER_CODE
            , LAST_NAME
            , FIRST_NAME
            , MIN(ORDER_DATETIME) AS ORDER_DATETIME
            , MIN(DATE (ORDER_DATETIME)) AS M_DATE 
        FROM
            EC_ORDERS 
        WHERE

            -- DATE関数で日付のみに編集してから7月の受注データを抽出
            DATE (ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31' 
        GROUP BY
            MEMBER_CODE 
    ) AS SUB 

    -- オプトアウト区分を取得するため、会員テーブルを内部結合
    INNER JOIN MEMBER_MST AS MM 
        ON SUB.MEMBER_CODE = MM.MEMBER_CODE 
WHERE
    -- ７月に初めて受注した日付から１年より前に、受注データがあるか存在チェック
    EXISTS ( 
        SELECT
            ORDER_NO 
        FROM
            EC_ORDERS 
        WHERE
            SUB.MEMBER_CODE = MEMBER_CODE 
            AND DATE (ORDER_DATETIME) < DATE (SUB.M_DATE, '-1 year')
    ) 

    -- ７月に初めて受注した前日から１年前までに、受注データが存在しないかチェック
    AND NOT EXISTS ( 
        SELECT
            ORDER_NO 
        FROM
            EC_ORDERS 
        WHERE
            SUB.MEMBER_CODE = MEMBER_CODE 
            AND DATE (ORDER_DATETIME) BETWEEN DATE (SUB.M_DATE, '-1 year') AND DATE (SUB.M_DATE, '-1 day')
    ) 
ORDER BY
    SUB.ORDER_DATETIME DESC
    , SUB.MEMBER_CODE DESC;

【問題4】販売額分析

WITH TOTAL_SAL AS ( 

    -- 2019年の販売総額を取得
    SELECT
        SUM(TOTAL_VALUE) AS TOTAL_AMT 
    FROM
        CONVENIENCE 
    WHERE
        SURVEY_YEAR = 2019 
        AND KIND_CODE = '100'
) 
, DECILE_GROUP AS ( 

    -- 2019年の都道府県毎の販売額と店舗数を取得
    SELECT
        C1.PF_CODE
        , C1.TOTAL_VALUE AS TOTAL_SAL
        , C2.TOTAL_VALUE AS TOTAL_SHOP

         -- NTILE関数で販売額の降順、店舗数の昇順、都道府県コードの昇順で30都道府県を10グループに振分け
        , NTILE(10) OVER (ORDER BY C1.TOTAL_VALUE DESC , C2.TOTAL_VALUE ASC ,C1.PF_CODE ASC) AS DC_NO 
    FROM
        CONVENIENCE AS C1 

        -- 2019年の店舗数を取得するため、同一テーブルを内部結合
        INNER JOIN CONVENIENCE AS C2 
            ON C1.PF_CODE = C2.PF_CODE 
            AND C2.SURVEY_YEAR = 2019 
            AND C2.KIND_CODE = '150' 
    WHERE
        C1.SURVEY_YEAR = 2019 
        AND C1.KIND_CODE = '100'
) 
, DC_SUMMARY AS ( 

    -- グループ分けした単位で、販売額と店舗数を集計
    SELECT
        DC_NO
        , SUM(TOTAL_SAL) AS DC_SAL
        , SUM(TOTAL_SHOP) AS DC_SHOP 
    FROM
        DECILE_GROUP 
    GROUP BY
        DC_NO
) 
SELECT
    DS.DC_NO AS NO
    , DS.DC_SAL AS TTL_SAL

    -- 小数点を計算するので、CAST関数でREAL型に変換して、結果をROUND関数で四捨五入する
    , ROUND( 
        (CAST(DS.DC_SAL AS REAL) / TS.TOTAL_AMT * 100)
        , 1
    ) AS PER_SAL
    , ROUND( 
        ( 
            CAST( 

                -- SUM OVERを使用して当該データまでの販売額の累計を取得
                SUM(DS.DC_SAL) OVER (ORDER BY DS.DC_NO ASC) AS REAL
            ) / TS.TOTAL_AMT * 100
        ) 
        , 1
    ) AS CUM_SAL

    -- 小数点切捨てのため、計算結果をCAST関数でINT型に再度変換
    , CAST((CAST(DS.DC_SAL AS REAL) / DS.DC_SHOP) AS INT) AS AVG_SAL 
FROM

    -- グループ分けした結果をメインテーブルとする
    DC_SUMMARY AS DS 

    -- 各行の計算で販売総額を使用するためにクロス結合
    CROSS JOIN TOTAL_SAL AS TS 
ORDER BY
    DS.DC_NO ASC;