近況報告

前回の SQL コンテストの後、1 日 1 問ペースで過去問のボス問題を解き、9 月上旬くらいには過去問 40 問全て埋めました。SQL を使って作ってみたいアプリ (?) とかもあるので余裕があったらやってみたいですね。

コンテストの振り返り

問題 1

久しぶりなので少し手惑いました。全てを大文字にするのは UPPER 関数みたいなものがありそうだなと思い、少し調べたら実際に見つかりました。開始約 2 分後に AC。

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

問題 2

各人についての最初のログイン日を記録したテーブルを作り、そのテーブル日付が 8 月の部分だけを見ればよいです。開始約 10 分後に AC。

WITH T AS(
    SELECT
        MEMBER_CODE,
        MIN(DATE(LOGIN_DATETIME)) AS MINDATE
    FROM
        ACCESS_LOG
    GROUP BY
        MEMBER_CODE
)
SELECT
    MINDATE AS FIRST_LOGIN,
    COUNT(*) AS MEMBER_CNT
FROM
    T
WHERE
    MINDATE BETWEEN '2023-08-01' AND '2023-08-31'
GROUP BY
    MINDATE
ORDER BY
    FIRST_LOGIN DESC

問題 3

まず最初に 7 月中の最初の受注日をまとめるテーブルを作り、その次に 7 月中の最初の受注日より 1 年以上前の受注日の MAX をまとめた表を作りました。最後に、2 番目に作った表で受注が NULL でない人だけ表示するというコードを書き、開始約 30 分後にサンプルが合ったので出してみますが WA が出ます。その後、なぜ落ちるのかわからずほぼ同じコードを投げてみますが通りません。しばらくして、1 年以上前にも、1 年以上前より後にも両方受注がある場合にバグることに気づきます。受注の MAX を　7 月中の最初の受注日より前に取り、その値が 1 年以上前であるのみ表示するように書き換えることで、開始約 52 分後に AC しました。

WITH T1 AS(
    SELECT
        MIN(ORDER_DATETIME) AS ORDER_DATE,
        MEMBER_CODE AS CODE
    FROM
        EC_ORDERS
    WHERE
        DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY
        MEMBER_CODE
),T2 AS(
    SELECT
        MAX(DATE(ORDER_DATETIME)) AS LAST_DATE,
        EC_ORDERS.MEMBER_CODE AS CODE
    FROM
        EC_ORDERS
        INNER JOIN
            T1 ON EC_ORDERS.MEMBER_CODE = T1.CODE 
    WHERE
        ORDER_DATETIME < 
        CASE
            WHEN ORDER_DATE IS NULL THEN '0000-01-01'
            ELSE ORDER_DATE
        END
    GROUP BY
        EC_ORDERS.MEMBER_CODE
)
SELECT
    ORDER_DATE,
    T1.CODE,
    CASE OPTOUT_TYPE
        WHEN 0 THEN '可'
        WHEN 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM
    T1
    INNER JOIN
        MEMBER_MST ON T1.CODE = MEMBER_MST.MEMBER_CODE,
        T2 ON T1.CODE = T2.CODE
WHERE
    LAST_DATE IS NOT NULL AND DATE(LAST_DATE) < DATE(ORDER_DATE, '-1 year')
ORDER BY
    ORDER_DATE DESC,
    T1.CODE DESC

問題 4

3 問目で時間を浪費してしまったため残り時間はほぼなかったのですが、間に合わなくても良いので見てみます。とりあえず問題文で示されている通りにソートしてから累積和を取ればいいので、表示項目は多いものの 1 つ 1 つは難しくなさそうという印象を受けました。累積和のパートは、計算量的には良くなさそうですが自己外部結合するのが一番頭を使わずに書けるので、その方法を取りました。しばらくしてサンプルが合ったので出してみますが、3 つあるテストケースのうち 1 つだけが通りません。実はデータのある都道府県数が 30 より多いのではないかという疑いを持って投げ直したりしましたが、何をやっても通らないのでとりあえず放置しました。コンテスト開催期間終了後に改めて見てみると、普通に最初のソートの比較がバグっているのに気づき、修正すると通りました。

WITH T1 AS(
    SELECT
        SUM(
            CASE KIND_CODE
                WHEN '100' THEN TOTAL_VALUE
                ELSE 0
            END
        ) AS VAL,
        SUM(
            CASE KIND_CODE
                WHEN '150' THEN TOTAL_VALUE
                ELSE 0
            END
        ) AS CNT,
        RANK() OVER(
            ORDER BY
                SUM(
                    CASE KIND_CODE
                        WHEN '100' THEN TOTAL_VALUE
                        ELSE 0
                    END
                ) DESC,
                SUM(
                    CASE KIND_CODE
                        WHEN '150' THEN TOTAL_VALUE
                        ELSE 0
                    END
                ) ASC,
                PF_CODE ASC
        ) AS RNK
    FROM
        CONVENIENCE
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        PF_CODE
), T2 AS(
    SELECT
        SUM(VAL) AS VAL,
        SUM(CNT) AS CNT,
        (RNK + 2) / 3 AS GRP
    FROM
        T1
    GROUP BY
        GRP
    ORDER BY
        GRP ASC
), T3 AS(
    SELECT
        T2.GRP,
        T2.VAL,
        T2.CNT,
        SUM(
            CASE
                WHEN MT2.GRP <= T2.GRP THEN MT2.VAL
                ELSE 0
            END
        ) AS CUMVAL,
        SUM(
            MT2.VAL
        ) AS ALLVAL
    FROM
        T2
        CROSS JOIN
            T2 AS MT2
    GROUP BY
        T2.GRP
)
SELECT
    GRP AS NO,
    VAL AS TTL_SAL,
    ROUND(100.0 * VAL / ALLVAL, 1) AS PER_SAL,
    ROUND(100.0 * CUMVAL / ALLVAL, 1) AS CUM_SAL,
    VAL / CNT AS AVG_SAL
FROM
    T3
WHERE
    NO <= 10
ORDER BY
    NO ASC

結果

52:38(+2) で問題 1,2,3 の 3 完 60 点で、35 位でした。

難易度的には全完できるセットだったとは思います。

感想

SQL コンテストでは自分でサンプルを作ってデバッグするということができないので、サンプルは合うものの通らないという状況の対処がかなり厳しく感じました。ただ、それ以前の問題として、問題文を正しく読むのと正しい論理を構成するのを心掛けたいです。

まとめ

次は全完

トークサクライ

第9回 SQLコンテスト (2023/10/13)