SQLZOO附加题练习 - Window functions

前言

SQLZOO里面的题还是比较适合初学者的，这里仅仅作为巩固基础，同时因为这道题目前搜不到相应的答案，所以做个小小的分享~
PS：非小白玩家可以去LeetCode、牛客网、CodeWars上刷题更佳

题目

General Elections were held in the UK in 2015 and 2017. Every citizen votes in a constituency. The candidate who gains the most votes becomes MP for that constituency.
All these results are recorded in a table ge

yr	firstName	lastName	constituency	party	votes
2015	Ian	Murray	S14000024	Labour	19293
2015	Neil	Hay	S14000024	Scottish National Party	16656
2015	Miles	Briggs	S14000024	Conservative	8626
2015	Phyl	Meyer	S14000024	Green	2090
2015	Pramod	Subbaraman	S14000024	Liberal Democrat	1823
2015	Paul	Marshall	S14000024	UK Independence Party	601
2015	Colin	Fox	S14000024	Scottish Socialist Party	197
2017	Ian	MURRAY	S14000024	Labour	26269
2017	Jim	EADIE	S14000024	SNP	10755
2017	Stephanie Jane Harley	SMITH	S14000024	Conservative	9428
2017	Alan Christopher	BEAL	S14000024	Liberal Democrats	1388

1.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.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.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) as posn
FROM 
	ge
WHERE constituency = 'S14000021'
ORDER BY party,yr

4.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.Show the parties that won for each Edinburgh constituency in 2017.

SELECT 
	a.constituency,
	a.party
FROM
(SELECT 
	constituency,
	party,
    RANK() over(partition by constituency order by votes DESC) as posn
FROM 
	ge
WHERE 
	constituency  BETWEEN 'S14000021' AND 'S14000026'
    AND yr  = 2017) a
WHERE a.posn = 1

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

# 这道题想了一会儿
SELECT
	a.party, 
	count(1)
FROM
(SELECT 
	constituency,
	party,
	RANK() over(partition by constituency order by votes DESC) as posn
 FROM 
 	ge
 WHERE 
 	constituency like 'S%'
    AND yr  = 2017) a 
WHERE a.posn =1
GROUP BY a.party