【問題1】販売額一覧

SELECT
    PF_CODE AS CODE
    , TOTAL_VALUE AS SALES_AMT 
FROM
    CONVENIENCE 
WHERE
    SURVEY_YEAR = 2019 
    AND KIND_CODE = '100' 
ORDER BY
    SALES_AMT DESC
    , PF_CODE ASC;

【問題2】販売額のデータ移行

INSERT 
INTO CONVENI_SALE_2018 
SELECT
    SURVEY_YEAR
    , PF_CODE
    , TOTAL_VALUE 
FROM
    CONVENIENCE 
WHERE
    SURVEY_YEAR = 2018 
    AND KIND_CODE = '100' 
    AND TOTAL_VALUE IS NOT NULL;

【問題3】最大世帯人員

SELECT
    H1.CLASS_NAME AS CLASS
    , H1.PERSON_NAME AS PERSON
    , H1.AMT AS HOUSEHOLDS 
FROM
    HOUSEHOLD AS H1 
WHERE
    H1.CLASS_CODE <> '01' 
    AND H1.PERSON_CODE <> '1' 
    AND H1.AMT = ( 
        SELECT
            MAX(H2.AMT) 
        FROM
            HOUSEHOLD AS H2 
        WHERE
            H2.CLASS_CODE = H1.CLASS_CODE 
            AND H2.CLASS_CODE <> '01' 
            AND H2.PERSON_CODE <> '1' 
        GROUP BY
            H2.CLASS_CODE
    ) 
ORDER BY
    H1.CLASS_CODE;

【問題4】順位計算

-- 誤答数
WITH PENALTY AS ( 
    SELECT
        S.ENTRY_ID
        , COUNT(SUBMIT_ID) AS WRONG_ANS 
    FROM
        SUBMISSIONS AS S 
        INNER JOIN ( 
            SELECT
                ENTRY_ID
                , PROBLEM_ID
                , MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT 
            FROM
                SUBMISSIONS 
            WHERE
                STATUS = 'AC' 
                AND ENTRY_ID IS NOT NULL 
                AND CONTEST_ID = 2 
            GROUP BY
                ENTRY_ID
                , PROBLEM_ID
        ) AS SUB1 
            ON S.ENTRY_ID = SUB1.ENTRY_ID 
            AND S.PROBLEM_ID = SUB1.PROBLEM_ID 
            AND S.SUBMITTED_AT < SUB1.LAST_SUBMITTED_AT 
    GROUP BY
        S.ENTRY_ID
)
-- 点数と解答時間
, SCORE_AND_TIME AS ( 
    SELECT
        ENTRY_ID
        , SUM(POINT) AS POINT
        , MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT 
    FROM
        SUBMISSIONS AS S 
    WHERE
        STATUS = 'AC' 
        AND ENTRY_ID IS NOT NULL 
        AND CONTEST_ID = 2 
    GROUP BY
        ENTRY_ID
) 
SELECT
    RANK() OVER ( 
        ORDER BY
            POINT DESC
            , STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(WRONG_ANS, 0) ASC
    ) AS RANK
    , E.USER_ID AS USER_ID
    , POINT AS POINT
    , STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(P.WRONG_ANS, 0) AS
     EX_TIME
    , IFNULL(WRONG_ANS, 0) AS WRONG_ANS 
FROM
    ENTRIES AS E 
    LEFT OUTER JOIN PENALTY AS P 
        ON E.ENTRY_ID = P.ENTRY_ID JOIN SCORE_AND_TIME AS SAT 
            ON E.ENTRY_ID = SAT.ENTRY_ID 
WHERE
    E.CONTEST_ID = 2 
ORDER BY
    RANK ASC
    , WRONG_ANS ASC
    , USER_ID ASC;