【問題1】点数差分

SELECT
    MEMBER_ID AS ID
    , (SCORE1 - SCORE2) AS DIFF 
FROM
    CONTEST_RESULTS 
WHERE

    -- 点数の差分を絶対値に変換して比較
    ABS(SCORE1 - SCORE2) >= 20 
ORDER BY
    DIFF DESC
    , ID DESC;

【問題2】受注件数

SELECT
    OD.CUST_CODE AS CODE
    , CS.CUST_NAME AS NAME

    -- DISTNCTで同一日付を1件とカウントする
    , COUNT(DISTINCT OD.ORDER_DATE) AS CNT 
FROM
    ORDERS AS OD 

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

【問題3】身長と体重

WITH AVG_TBL AS ( 

   -- 年齢、性別毎に身長と体重の平均値を算出
    SELECT
        AGE
        , GENDER_CODE

          -- ROUND関数で小数点第2位を四捨五入
        , ROUND( 
            AVG( 

                -- カテゴリコードが10(身長)のデータが対象
                CASE CATEGORY_CODE 
                    WHEN 10 THEN AVERAGE_VALUE 
                    END
            ) 
            , 1
        ) H_AVG
        , ROUND( 
            AVG( 

                -- カテゴリコードが20(体重)のデータが対象
                CASE CATEGORY_CODE 
                    WHEN 20 THEN AVERAGE_VALUE 
                    END
            ) 
            , 1
        ) W_AVG 
    FROM
        SCHOOL_HEALTH 
    WHERE
        SURVEY_YEAR = 2019 
    GROUP BY
        AGE
        , GENDER_CODE
) 
SELECT
    AVG_TBL.AGE

      -- CASE句で性別の表示内容を振分け
    , CASE AVG_TBL.GENDER_CODE 
        WHEN 20 THEN 'MALE' 
        ELSE 'FEMALE' 
        END GENDER
    , MAX(H_AVG) H_AVG
    , MAX(W_AVG) W_AVG

      -- 計算結果を四捨五入
    , ROUND( 

        -- 平均値以上の件数をカウント(100.0を掛けて小数点以下の計算を可能とする)
        100.0 * COUNT( 
            CASE 
                WHEN SH.CATEGORY_CODE = 10 
                AND H_AVG <= SH.AVERAGE_VALUE 
                    THEN 1 
                END

       -- カテゴリコードが身長のデータの全件をカウント
        ) / COUNT(CASE WHEN SH.CATEGORY_CODE = 10 THEN 1 END)
        , 1

    -- %を結果に付加
    ) || '%' H_PER

      -- 体重も身長と同様に計算
    , ROUND( 
        100.0 * COUNT( 
            CASE 
                WHEN SH.CATEGORY_CODE = 20 
                AND W_AVG <= SH.AVERAGE_VALUE 
                    THEN 1 
                END
        ) / COUNT(CASE WHEN SH.CATEGORY_CODE = 20 THEN 1 END)
        , 1
    ) || '%' W_PER 
FROM
    AVG_TBL 

    -- 学校保健データを外部結合して比較する平均値を取得
    LEFT OUTER JOIN SCHOOL_HEALTH AS SH 
        ON AVG_TBL.AGE = SH.AGE 
        AND AVG_TBL.GENDER_CODE = SH.GENDER_CODE 
        AND SURVEY_YEAR = 2019 
GROUP BY
    AVG_TBL.AGE
    , AVG_TBL.GENDER_CODE 
ORDER BY
    AVG_TBL.AGE DESC
    , AVG_TBL.GENDER_CODE DESC;

【問題4】人気順位

-- 人気順位を全てゼロクリア
UPDATE ITEM 
SET
    ITEM_POPULAR_RANK = 0;

WITH SUM_QTY_WK AS ( 

    -- 商品毎の受注数量の集計と最大受注日を取得
    SELECT
        ODD.ITEM_CODE
        , SUM(ODD.ORDER_QTY) AS SUM_QTY
        , MAX(OD.ORDER_DATE) AS MAX_DATE 
    FROM
        ORDERS AS OD 
        INNER JOIN ORDERS_DTL AS ODD 
            ON ODD.ORDER_NO = OD.ORDER_NO 
    WHERE
        OD.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30' 
    GROUP BY
        ODD.ITEM_CODE
) 
, RANK_ITEM_WK AS ( 

    -- 上記で集計した結果を基に受注数量合計、最大受注日、商品コードの降順で順位を求める
    SELECT
        ITEM_CODE
        , ROW_NUMBER() OVER ( 
            ORDER BY
                SUM_QTY DESC
                , MAX_DATE DESC
                , ITEM_CODE DESC
        ) AS POPULAR_RANK 
    FROM
        SUM_QTY_WK
) UPDATE ITEM 
SET
    ITEM_POPULAR_RANK = RIW.POPULAR_RANK 
FROM
    ( 

        -- 副問合せで上記で作成した仮想テーブルの商品に対応する人気順をセット
        SELECT
            ITEM_CODE
            , POPULAR_RANK 
        FROM
            RANK_ITEM_WK
    ) AS RIW 
WHERE
    ITEM.ITEM_CODE = RIW.ITEM_CODE;