コンテストの制限時間が終了しました。
以降も提出を行うことができますが、順位集計には反映されません。
以降も提出を行うことができますが、順位集計には反映されません。
【問題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;