【問題1】下線取除き

SELECT
    EMP_CODE AS CODE

     -- 項目をシングルクォーテーションで囲むため、QUOTE関数を使用
    , QUOTE(TRIM(EMP_KANA_NAME,'_')) AS KANA_NAME 
FROM
    EMP 
ORDER BY
    EMP_CODE DESC;

【問題2】優良顧客

SELECT
    OD.CUST_CODE AS CODE
    , CS.CUST_NAME AS NAME
    , COUNT(OD.ORDER_NO) AS CNT
    , SUM(OD.ORDER_AMNT) AS TTL_AMT

    -- 平均受注金額をROUND関数で四捨五入（小数点第1位のためパラメータを省略）
    , ROUND(AVG(OD.ORDER_AMNT)) AS AVG_AMT 
FROM
    ORDERS AS OD 

    -- 顧客名を取得するため、顧客テーブルを内部結合
    INNER JOIN CUSTOMER AS CS 
        ON CS.CUST_CODE = OD.CUST_CODE 
WHERE
    OD.ORDER_DATE BETWEEN '2023-09-01' AND '2023-09-30' 
GROUP BY
    OD.CUST_CODE
    , CS.CUST_NAME 

-- 受注件数が5件以上の顧客のみ対象
HAVING
    CNT >= 5 
ORDER BY
    CNT DESC
    , AVG_AMT DESC
    , CODE ASC

-- 上位5件まで表示
LIMIT 5;

【問題3】食料費の割合

WITH MAIN_2022 AS ( 
    -- 2022年のデータを集計
    SELECT
        AREA_CODE
        , ROUND( 
            -- REAL型に変換して計算
            CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100
            , 1
        ) AS RATIO
          -- DENSE_RANK関数で、比率の昇順で並べて欠番なしで採番をおこなう
        , DENSE_RANK() OVER ( 
            ORDER BY
                ROUND( 
                    CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100
                    , 1
                ) ASC
        ) AS RANK 
    FROM
        HOUSEHOLD_SURVEY 
    WHERE
        SURVEY_YEAR = 2022
) 
, SUB_2017 AS ( 
    -- 上記と同様に2017年のデータを集計
    SELECT
        AREA_CODE
        , ROUND( 
            CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100
            , 1
        ) AS RATIO
        , DENSE_RANK() OVER ( 
            ORDER BY
                ROUND( 
                    CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100
                    , 1
                ) ASC
        ) AS RANK 
    FROM
        HOUSEHOLD_SURVEY 
    WHERE
        SURVEY_YEAR = 2017
) 
, SUB_2012 AS ( 
    -- 上記と同様に2012年のデータを集計
    SELECT
        AREA_CODE
        , ROUND( 
            CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100
            , 1
        ) AS RATIO
        , DENSE_RANK() OVER ( 
            ORDER BY
                ROUND( 
                    CAST(FOOD_EXP AS REAL) / CONSUMPTION_EXP * 100
                    , 1
                ) ASC
        ) AS RANK 
    FROM
        HOUSEHOLD_SURVEY 
    WHERE
        SURVEY_YEAR = 2012
) 
SELECT
    M22.AREA_CODE AS CODE
    , AREA_NAME AS NAME
      -- 表示名の先頭が数字のためシングルクォーテーションで囲む
    , M22.RANK AS '2022_RANK'
    , M22.RATIO || '%' AS '2022_RATIO'
    , S17.RANK AS '2017_RANK'
    , S17.RATIO || '%' AS '2017_RATIO'
    , S12.RANK AS '2012_RANK'
    , S12.RATIO || '%' AS '2012_RATIO'
FROM
    MAIN_2022 AS M22 
    -- 各年で集計したデータを内部結合
    INNER JOIN SUB_2017 AS S17 
        ON S17.AREA_CODE = M22.AREA_CODE 
    INNER JOIN SUB_2012 AS S12 
        ON S12.AREA_CODE = M22.AREA_CODE 
    -- 地域名を取得するため、地域テーブルを内部結合
    INNER JOIN AREA 
        ON AREA.AREA_CODE = M22.AREA_CODE 
ORDER BY
    M22.RANK
    , M22.AREA_CODE DESC;

【問題4】顧客行動分析

WITH SUB1 AS ( 

    -- SESSION_ID ごとに EX_TIMESTAMP 順にランクづけし、STEP N と RANK M を一覧にする
    SELECT
        SESSION_ID
        , PROCESS_ID
        , RANK() OVER (PARTITION BY SESSION_ID ORDER BY EX_TIMESTAMP) AS RANK 
    FROM
        PROCESS_LOG 
    ORDER BY
        SESSION_ID ASC
        , EX_TIMESTAMP ASC
) 
, SUB2 AS ( 

    -- STEP の番号 N とランク M が異なる MIN まではカウント対象
    SELECT
        SESSION_ID
        , MIN(RANK) AS MIN 
    FROM
        SUB1 
    WHERE
        CAST(SUBSTR(SUB1.PROCESS_ID, 5, 1) AS INT) != RANK 
    GROUP BY
        SESSION_ID
) 

SELECT
    PROCESS_ID AS PROCESS

    -- SUB2 を外部結合し、MIN 未満の STEP を数え上げる
    -- このとき、MIN が NULL の場合は存在する STEP を全て数え上げる
    , COUNT( 
        DISTINCT CASE 
            WHEN CAST(SUBSTR(SUB1.PROCESS_ID, 5, 1) AS INT) < SUB2.MIN 
            OR SUB2.MIN IS NULL 
                THEN SUB1.SESSION_ID 
            END
    ) AS CNT 
FROM
    SUB1
    LEFT OUTER JOIN SUB2 
        ON SUB1.SESSION_ID = SUB2.SESSION_ID 
GROUP BY
    PROCESS_ID 
ORDER BY
    PROCESS_ID;