エンジニアへの挑戦状 #04 新型ウイルス感染者数をSQLで抽出

あなたのSQL基礎力を測る問題です。
データベースに慣れると、Excelでは扱えないような大きなデータも難なく捌けるように。さて、あなたは解けますか？
所要時間：3～5分
SQLに親しんでいる方なら、1分足らずで解けるかも！？

【問題】

新型ウイルスの感染拡大によって、私達のライフスタイルが大きく変わりました。世界中を巻き込む行動制限や消費活動にも影響があります。日本では、厚生労働省が都道府県別の感染者数を CSV 形式で公開しています。

マーケティング担当者のあなたは、自社の売上に与えた影響を資料に作成するために、感染者数の集計を依頼されました。集計作業をコードに残して再現しやすくするために、データベースを利用します。SQLite3 で、次のようにしてテーブルを作成しました。

CREATE TABLE covid19 (
    case_date TIMESTAMP,
    all_case INTEGER,
    hokkaido INTEGER,
    aomori INTEGER,
    iwate INTEGER,

・・・

    saitama INTEGER,
    chiba INTEGER,
    tokyo INTEGER,
    kanagawa INTEGER,

・・・

    okinawa INTEGER
);

.import ./newly_confirmed_cases_daily.csv covid19 --csv --skip 1

例えば、次のような SQL を実行すると、発症日、日本全国での感染者数、東京での感染者数を取得できます。

SELECT
  case_date,
  all_case,
  tokyo
FROM
  covid19
;

設問１：

感染者数がピークの時にどんな影響があったのかを調べるために、日本全国で感染者数が多かった日、その時に全国で感染した人数、その時に東京で感染した人数を資料に載せようと思います。
以下の選択肢から、正しい値を得ることができる SQL はどれでしょうか？

【選択肢1】

SELECT
  case_date,
  all_case,
  tokyo
FROM
  covid19
ORDER BY all_case DESC
LIMIT 1;

【選択肢2】

SELECT
  case_date,
  all_case,
  tokyo
FROM
  covid19
WHERE
  all_case = max(all_case)
;

【選択肢3】

SELECT
  case_date,
  all_case,
  tokyo
FROM
  covid19
WHERE
  all_case = (
    SELECT
      max(all_case)
    FROM
      covid19
    )
;

設問2：

ピークの影響と、そうでない日の比較をするための参考材料として、100番目に感染者が多かった日を調べようと思います。感染者数と日付を取得するSQLを完成させてください。「ここに適切なSQLを追加する」に書くべきSQLは何でしょうか？

SELECT
  case_date,
  all_case
FROM (
  SELECT
    case_date,
    all_case,
    「ここに適切なSQLを追加する」 as number
  FROM
    covid19
)
WHERE number = 100
;

答えはわかりましたか？時間がかかってしてまった方や、つまずきを感じた方はもう一度SQLを復習してみましょう。

▼エンジニアスタイルのSQLに関する記事はこちら
VBA内でSQLを扱うコツ【連載】実務で使えるAccessのコツ
 VBA＋SQLで作るAccessデータベースアプリケーション
 データベースのしくみと使い方を知る：エンジニアが生き残るためのテクノロジーの授業 #4

正解とその解説を掲載しました。答え合わせしてみてくださいね。>>正解と解説はこちら

【出題者】
早川敦士さん
株式会社DATAFLUCTで事業責任者（プロダクトマネージャー）として従事。データと機械学習をビジネスに活かすプロダクトを開発している。また、株式会社ホクソエムで執行役員を務めたり、プログラミング教育に携わる。新卒でリクルートコミュニケーションズに入社しWeb広告やマーケティングオートメーションなどのB2Cマーケティングを経験し、その後ユーザベースにてB2BマーケティングプラットフォームであるFORCASのクローラーやデータ分析基盤を開発するチームリーダーをする。大学在学中に『データサイエンティスト養成読本』（技術評論社刊）を共著にて執筆。その後も『機械学習のための特徴量エンジニアリング』（オライリー・ジャパン刊）や『Pythonによるはじめての機械学習プログラミング』（技術評論社刊）などで執筆活動を続けている。YouTubeチャンネル『データサイエンティストgepuro』で動画を投稿。
Twitterアカウント

▼早川敦士さんのその他の記事はこちら
Pythonでデータ処理と機械学習を始めよう
 機械学習の仕事内容って？実はコードを書くだけじゃない！【漫画】未経験なのに、機械学習の仕事始めました
 エンジニアへの挑戦状 #02　Pythonでお宝探し