TOPSIC SQL CONTESTに突貫した感想

概要

TOPSIC SQL CONTEST というのが最近やっていたので、参加してみました。自身のSQL力を図るのが目的のイベントらしいので、SQLほとんど書いたことのない私でどれだけやれるのかが気になったのもあり、参加を決断しました。
結果は、60/100点を45分程度で解くことが出来ました。

自身のSQL経験

• 業務経験なし
• 新入社員研修で僅かに触った程度
• SELECT,INSERT,JOINくらいは何をしているのかはわかる（記法は知らない）
• 事前に練習用コンテストを3問目まで解いた(80minかかった)
• サブクエリは感覚で使っている

本番感想

目標は60分の制限時間で4問中3問を解くことでした。

1問目:10点

都道府県ごとのコンビニのデータから、2019年度かつ販売額のデータを抽出して、販売額順に並べる問題。

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

練習問題だとスニペットが全角になっていたのに、半角になっていることでちょっと感動しました（無意味）。
ASCとDESCがそもそも出てこなくて調べたのは内緒

2問目:20点

コンビニのデータから、2018年の販売額データを2018年のデータを格納している別のテーブルに移し替える問題。

練習問題に出てこなかったINSERTを使用する問題。コードテストをしても結果が出てこないので苦労しました。
INSERT対象をSELECTで表示すれば見れたよっていうのはこの文章を書いているときに気が付きました
ではじめに出したのがこれです。

INSERT INTO CONVENI_SALE_2018 (SURVEY_YEAR, PF_CODE, TOTAL_VALUE) 
SELECT SURVEY_YEAR, PF_CODE, TOTAL_VALUE FROM CONVENIENCE WHERE KIND_CODE = "100" AND TOTAL_VALUE NOT NULL

これでは、いけませんね。
手が滑って2018年以外のデータも移してしまいました。
制約を見ていたら気付けたのですぐ修正して提出できました。

3問目:30点

世帯人数＆収入別データから、収入ごとにどの世帯人数が最大の世帯数化を調べてそれを収入順に並べて抽出する問題。
このあたりから初心者の付け焼刃では厳しくなってきます。
まず私はGROUP BY句を当時知りませんでした。今でもよくわかってない
とりあえず今の私には無理だと判断してググります。
そうしてこんなサイトを見つけました。
なんか上のほうにいくつか書いてあったのですが、ここの方法3で紹介されていた、NOT EXISTSを使用する方法で行こうと思い立ちました。
そうして完成したコードがこれです。

SELECT CLASS_NAME AS CLASS, PERSON_NAME AS PERSON, AMT AS HOUSEHOLDS
FROM HOUSEHOLD WHERE NOT EXISTS
(SELECT 1 FROM (SELECT * FROM HOUSEHOLD WHERE PERSON_CODE != "1")
AS SUB WHERE HOUSEHOLD.CLASS_NAME = SUB.CLASS_NAME AND HOUSEHOLD.AMT < SUB.AMT)
AND PERSON_CODE != "1" AND CLASS_CODE != "01"

このコードはとんでもない初心者が書いたもので、尋常でなく見にくいです。
適当に意味も分からずぺたぺたコピペしていたら問題文についてきたテストケースが通ってしまったので、
（お？余裕か？） などど思いながら意気揚々とこのコードを提出しました。

TOPSIC「ハイだめー（WA：テストケース1/2）」

なんと、この問題には二つのテストケースのうち片方だけ通るようなひっかけがあったのです （そりゃあるでしょ）！
とりあえずサブサブクエリの抽出条件をもっと厳しくして、もとのクエリのFROM先も同じくらい厳しくした以下のコードを提出しました。

SELECT CLASS_NAME AS CLASS, PERSON_NAME AS PERSON, AMT AS HOUSEHOLDS
FROM (SELECT * FROM HOUSEHOLD WHERE PERSON_CODE != "1" AND CLASS_CODE != "01") AS T WHERE NOT EXISTS
(
SELECT 1 FROM (SELECT * FROM HOUSEHOLD WHERE PERSON_CODE != "1" AND CLASS_CODE != "01") AS SUB 
WHERE T.CLASS_CODE = SUB.CLASS_CODE AND T.AMT < SUB.AMT
)

なお、通らないもよう
さすがに絶望して、残り30分あったので一回4問目をチラ見することにしたのでした。

4問目チラ見:40点

詰まったので4問目に希望があるかをチラ見しに行きます。。。
わーすごい！今日からあなたはTOPSICの中の人だよ！頑張っていってね！
ちょっと地獄みたいな問題文が見えたので撤退します。
これより3問目つかむ方がチャンスあるぞ！

3問目見直し

うんとこどっこい唸っていました。
光明がマジで見えない中、あきらめムードで制約を眺めていたときです。
収入順に並べての部分を見落としていたことに気が付きました。
問題文付属のテストケースはもともと収入順に並んでいたので、これかもしれないとお祈りでコードを書いて提出しました。

SELECT CLASS_NAME AS CLASS, PERSON_NAME AS PERSON, AMT AS HOUSEHOLDS
FROM (SELECT * FROM HOUSEHOLD WHERE PERSON_CODE != "1" AND CLASS_CODE != "01") AS T WHERE NOT EXISTS
(
SELECT 1 FROM (SELECT * FROM HOUSEHOLD WHERE PERSON_CODE != "1" AND CLASS_CODE != "01") AS SUB 
WHERE T.CLASS_CODE = SUB.CLASS_CODE AND T.AMT < SUB.AMT
)
ORDER BY CLASS_CODE ASC

無事ACを獲得できました。よかった～

4問目

時間が15分ほど余ったので、一応やろうと4問目にチャレンジしました。
やりたいことはここに書くには長すぎるので、是非サイトに行って問題を見よう（宣伝）！
ちなみに私は、 （窓関数ってなんだ？） と考えているうちに制限時間になりました。

順位とまとめ

順位	合計点数	解答時間	誤答数
71	60	43:17	5

(精神的に)疲れました。 思ったより点が取れたのと、テストを受けたほかの方に聞いても4問目は難しかったとのことなので、ほぼ初心者でここまで行けたのはよかったのかなと思っています。
第4回があったら、是非参加したいです。
多分4問目は取れないと思いますが、スピードも順位に関係があるので、よりスピードを上げていきたいと考えています。

10/21追記

3問目のテストケースに誤りがあり、本来正答となるものの一部が、誤答として扱われるようになっていたようです。
修正＆リジャッジされ、順位も変動しました。
私の順位は～　136位！
これが実力ってことよ（）