SQLZOOのCOVID 19を解いてみた

さいきんSQLを勉強していまして、Gunosyの「非エンジニアが最速でSQLをマスターする方法」に書いてある『10年戦えるデータ分析入門』→SQLZOO (https://sqlzoo.net/wiki/SQL_Tutorial) という順番で学習を進めています。

　SQLZOOは出力が合っているのか違っているのかは教えてくれるのですが、どんなSQL文を書けば正解できるのか教えてくれません。すでに日本語でSQLZOOの解答を公開してくださっている方もおられますが、私も自分の勉強のアウトプットとして、解答例を公開したいと思います。

　SQLZOOは問題文が英語なので、あまり自信はありませんが問題文の和訳も用意しました。意訳なので正確さに欠けるかもしれませんが……

SQLZOOのWindow functionsの解答例はこちら　→　https://rnsr0371.boy.jp/2020/12/23/sqlzoowindow_functions/

Introduction the covid table

この例ではWHERE節が3月のイタリアの症例を表示するように使われています。クエリをスペインのでデータを表示するように変更してください。

SELECT
    name
    , DAY(whn)
    , confirmed
    , deaths
    , recovered
FROM covid
WHERE name = 'Spain'
    AND MONTH(whn) = 3;

Introducing the LAG function

LAG関数は直前の行かテーブルからのデータを表示するために使います。行を列挙するときは、データを国名で区切り、whnでソートします。つまり、イタリアのデータのみが考慮されます。前日に感染が確認された人を表示するようにクエリを修正してください。

SELECT
    name
    , DAY(whn) AS day
    , confirmed
    , LAG(confirmed, 1) OVER(PARTITION BY name ORDER BY whn) 
FROM covid
WHERE name = 'Italy'
    AND MONTH(whn) = 3
ORDER BY whn;

Number of new cases

症例数は累積的なものです。しかし、LAG関数を使えば、1日1日の新しく報告された患者数を知ることができます。3月のイタリアの一日ごとの新規症例数を表示してください。

SELECT
    name
    , DAY(whn)
    , confirmed - LAG(confirmed, 1) OVER(PARTITION BY name  ORDER BY whn) AS new
FROM covid
WHERE name = 'Italy'
    AND MONTH(whn) = 3;

Weekly changes

集められたデータは必然的に推定値であり、不正確です。しかし、時間軸を長く取れば、いくつかの問題を軽減することができます。WHERE WEEKDAY(whn) = 0で月曜日だけのデータを抽出することができます。各週のイタリアの新規感染者数を表示してください。つまり、月曜日だけを表示してください。

SELECT
    name
    , DATE_FORMAT(whn, '%Y-%m-%d') AS date
    , confirmed - LAG(confirmed, 1) OVER(PARTITION BY name ORDER BY whn) AS new_this_week
FROM covid
WHERE name = 'Italy'
    AND WEEKDAY(whn) = 0;

LAG using a JOIN

DATEの計算を使うことでテーブルをJOINすることができます。もしデータが欠損していたら、異なる結果が得られるでしょう。各週のイタリアの新規感染者数を表示してください。つまり、月曜日だけ表示してください。サンプルクエリ内でDATE_ADD関数を使って今週と先週をJOINしています。

SELECT
    tw.name
    , DATE_FORMAT(tw.whn, '%Y-%m-%d') AS date
    , tw.confirmed - lw.confirmed AS new_this_week
FROM covid AS tw 
LEFT JOIN covid AS lw ON DATE_ADD(lw.whn, INTERVAL 1 WEEK) = tw.whn AND tw.name = lw.name
WHERE tw.name = 'Italy'
    AND WEEKDAY(tw.whn) = 0;

RANK()

以下のクエリは症例数の世界ランキングと一緒に症例数を表示します。アメリカが最も多く、スペインが2位で……スペインは症例数では2番目ですが、イタリアはウイルスによる死者数が2番目であることに注意してください。テーブルに死者数のランキングを含めてください。

SELECT
    name
    , confirmed
    , RANK() OVER(ORDER BY confirmed DESC) AS rank_confirmed
    , deaths
    , RANK() OVER(ORDER BY deaths DESC) AS rank_deaths
FROM covid
WHERE whn = '2020-04-20'
    ORDER BY rank_confirmed;

Infection rate

以下のクエリはworldテーブルをJOINしているため、各国の総人口にアクセスし、感染率（10万にあたりの症例数）を計算することができます。各国の感染率のランキングを表示してください。最低1000万人以上の人口を持つ国だけを表示してください。

SELECT
    c.name
    ,  ROUND(100000 * c.confirmed / w.population, 0) AS infection_rate
    , RANK() OVER(ORDER BY c.confirmed / w.population) AS rank
FROM covid AS c
    JOIN world AS w ON c.name = w.name
WHERE c.whn = '2020-04-20' AND w.population > 10000000
ORDER BY w.population DESC;

Turning the corner

1日で1000人以上の新規感染者が出たことのある国において、新規感染者数のピークの日を示してください。

SELECT
    name
    , DATE_FORMAT(whn, '%Y-%m-%d') AS date
    , new
FROM
        (SELECT
            *
            , RANK() OVER(PARTITION BY name ORDER BY new DESC) AS rank
        FROM
            (SELECT
                name
                , whn
                , confirmed - LAG(confirmed, 1) OVER(PARTITION BY name ORDER BY whn) AS new
            FROM covid
            ) AS tmp
        WHERE new >= 1000) AS tmp2
WHERE rank =1
ORDER BY date;

＊抽出したデータそのものはおそらく正しいのですが、期待される出力には謎のソートがかけられていて、正答になりません。

Follow me!