【SQL練習】【SQLZOO】 9+ COVID 19

9+ COVID 19

問題はこちらです。
Window LAG - SQLZOO


以下が解答となります。

1.Introducing the covid table

【問題】
The example uses a WHERE clause to show the cases in 'Italy' in March.

この例では、WHERE句を使用して、3月の「イタリア」のケースを表示しています。

Modify the query to show data from Spain

スペインのデータを表示するようにクエリを変更してください。

【解答】

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

2.Introducing the LAG function

【問題】
The LAG function is used to show data from the preceding row or the table. When lining up rows the data is partitioned by country name and ordered by the data whn. That means that only data from Italy is considered.

LAG関数は、前の行またはテーブルのデータを表示するために使用されます。
行を並べるとき、データは国名で分割され、データの順序で並べられます。
つまり、イタリアのデータのみが考慮されます。

Modify the query to show confirmed for the day before.

前日の確認済みを表示するようにクエリを変更してください。

【解答】

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

3.Number of new cases

【問題】
The number of confirmed case is cumulative - but we can use LAG to recover the number of new cases reported for each day.

確認されたケースの数は累積されますが、LAG関数を使用して、毎日報告された新しいケースの数を回復することができます。

Show the number of new cases for each day, for Italy, for March.

3月のイタリアでの毎日の新規症例数を表示してください。

【解答】

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

4.Weekly changes

【問題】
The data gathered are necessarily estimates and are inaccurate. However by taking a longer time span we can mitigate some of the effects.
You can filter the data to view only Monday's figures WHERE WEEKDAY(whn) = 0.

収集されたデータは必然的に推定値であり、不正確です。ただし、より長い期間を取ることで、いくつかの影響を軽減できます。
データをフィルタリングして、月曜日の数値のみを表示できます。WHERE WEEKDAY（whn）= 0

Show the number of new cases in Italy for each week - show Monday only.

毎週のイタリアでの新しい症例の数を表示してください。- 月曜日のみを表示してください。

【解答】

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

5.LAG using a JOIN

【問題】
You can JOIN a table using DATE arithmetic. This will give different results if data is missing.

DATE演算を使用してテーブルを結合することできます。これにより、データが欠落している場合に異なる結果が得られます。

Show the number of new cases in Italy for each week - show Monday only.

毎週のイタリアでの新しい症例の数を表示してください。- 月曜日のみを表示してください。

【解答】

SELECT tw.name, DATE_FORMAT(tw.whn,'%Y-%m-%d'), 
 tw.confirmed - lw.confirmed
 FROM covid tw LEFT JOIN covid 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
ORDER BY tw.whn;

6.RANK()

【問題】
The query shown shows the number of confirmed cases together with the world ranking for cases.
United States has the highest number, Spain is number 2...
Notice that while Spain has the second highest confirmed cases, Italy has the second highest number of deaths due to the virus.

表示されるクエリは、確認されたケースの数と、ケースの世界ランキングを示しています。
米国が最も多く、スペインが2番目です...
スペインで2番目に確認された症例がありますが、イタリアではウイルスによる死亡者数が2番目に多いことに注意してください。

Include the ranking for the number of deaths in the table.

表に死亡者数のランキングを含めてください。

【解答】

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

7.Infection rate

【問題】
The query shown includes a JOIN t the world table so we can access the total population of each country and calculate infection rates (in cases per 100,000).

表示されるクエリには、JOIN t the worldテーブルが含まれているため、各国の総人口にアクセスして、感染率を計算できます（100,000あたりの場合）。

Show the infect rate ranking for each country. Only include countries with a population of at least 10 million.

各国の感染率ランキングを表示してください。人口が1,000万人以上の国のみを含めてください。

【解答】

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

8.Turning the corner

【問題】
For each country that has had at last 1000 new cases in a single day, show the date of the peak number of new cases.

1日に1000件以上の新規症例が発生した国ごとに、新規感染者数がピークに達した日付を表示してください。

【解答】

SELECT y.name,
       DATE_FORMAT(y.whn,'%Y-%m-%d'),
       y.peakNewCases 
 FROM (SELECT x.name,
              x.whn,
              RANK() OVER(PARTITION BY name ORDER BY peakNewCases DESC) AS rk,
              x.peakNewCases 
        FROM (SELECT name,
                     whn,
                     (confirmed - LAG(confirmed, 1) OVER(PARTITION BY name ORDER BY whn)) AS peakNewCases
               FROM covid
             ) AS x
      ) AS y
WHERE y.rk = 1 
  AND y.peakNewCases >= 1000
ORDER BY y.whn;