SQLZOO_Window functions

       感觉窗口函数是sqlzoo里比较难的一块，于是写下这篇文章，共同学习。

这块的题目不在sqlzoo主页的左侧栏目，而是在下方，我也是刷完左侧题目后面才发现的。由于sql代码很容易懂，就不做过多解释了，详见代码。

1、Warming up
Show the lastName, party and votes for the constituency ‘S14000024’ in 2017.

SELECT lastName, party, votes
FROM ge
WHERE constituency = 's14000024' AND yr = 2017
ORDER BY votes DESC

2、Who won?
Show the party and RANK for constituency S14000024 in 2017. List the output by party

SELECT 
party
,votes
,RANK() OVER (ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party

3、PARTITION BY
Use PARTITION to show the ranking of each party in S14000021 in each year. Include yr, party, votes and ranking (the party with the most votes is 1).

SELECT 
yr
,party
,votes
,RANK() OVER (PARTITION BY yr ORDER BY votes DESC) posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party,yr

4、Edinburgh Constituency
Use PARTITION BY constituency to show the ranking of each party in Edinburgh in 2017. Order your results so the winners are shown first, then ordered by constituency.

SELECT 
constituency
,party
,votes
,rank() over(partition by constituency order by votes desc) as posn
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr  = 2017
ORDER BY posn,constituency

5、Winners Only
Show the parties that won for each Edinburgh constituency in 2017.

select constituency,party
from
(	select 
	constituency
	,party
	,rank() over(partition by constituency order by votes desc) posn
  	from ge
 	where constituency between 'S14000021' 
 	and 'S14000026' 
 	and yr  = 2017
) a
where posn=1

6、Scottish seats
Show how many seats for each party in Scotland in 2017.

SELECT party,COUNT(*) 
FROM 
(
	SELECT 
	constituency
	,party
	,votes
	,RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) posn
  	FROM ge
	WHERE constituency LIKE 'S%'
    AND yr  = 2017) a
WHERE posn=1
GROUP BY party