SQLZOO:COVID-19

1.The example uses a WHERE clause to show the cases in ‘Italy’ in March.

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.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.

Modify the query to show confirmed for the day before.

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

• 新知识点：lag(ColumnName,n,m)函数用于选取某一列的对应上n行的数据，如果没有数据则返回m。如果没有输入m则返回null。如果没有输入n、m值，默认返回上一行的数据。over()用于对数据进行分组。
  • 拓展：lead()函数用于选取当前记录对应下n行的数据。
• DAY()函数用于选取日期中的“日”期，同理可推MONTH()和YEAR()。

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

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

SELECT name