继上篇博客，关于窗口函数

1、Introducing the covid table
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
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) dbf
FROM covid
WHERE name = 'Italy'
AND MONTH(whn) = 2

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

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

4、Weekly changes
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) new
FROM covid
WHERE name = 'Italy'
AND WEEKDAY(whn) = 0
ORDER BY whn

5、LAG using a JOIN
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()
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) 
FROM covid
WHERE whn = '2020-04-20' 
ORDER BY confirmed DESC

7、Infection rate
Show the infect rate ranking for each country. Only include countries with a population of at least 10 million.
答案是错的，仅供参考

SELECT 
world.name,
ROUND(100000*confirmed/population,0) as t,
rank() over(order by t) 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.
有些题目不是很理解，此题也没有做出来。