SQLZOO Window Function Solutions

SQLZOO Window Function Solutions

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


Who won?
2.
You can use the RANK function to see the order of the candidates. If you RANK using (ORDER BY votes DESC) then the candidate with the most votes has rank 1.

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


PARTITION BY
3.
The 2015 election is a different PARTITION to the 2017 election. We only care about the order of votes for each year.
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


Edinburgh Constituency
4.
Edinburgh constituencies are numbered S14000021 to S14000026.
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 r
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
ORDER BY r , constituency


Winners Only
5.
You can use SELECT within SELECT to pick out only the winners in Edinburgh.
Show the parties that won for each Edinburgh constituency in 2017.

select constituency,party from (SELECT constituency,party, votes,RANK() over (partition by constituency order by votes desc) as r
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
ORDER BY r , constituency
) x
where x.r=1


Scottish seats
6.
You can use COUNT and GROUP BY to see how each party did in Scotland. Scottish constituencies start with 'S'
Show how many seats for each party in Scotland in 2017.

select party,count(1) from (select constituency,party from (SELECT constituency,party, votes,RANK() over (partition by constituency order by votes desc) as r
  FROM ge
 WHERE constituency like 'S%'
   AND yr  = 2017
ORDER BY r , constituency
) x
where x.r=1) y
group by party

Task 6 code gives error as shown below, please check it

Error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

The correct answer is :
https://zhuanlan.zhihu.com/p/114800754

SELECT party , COUNT(*)
FROM ge x
WHERE constituency like 'S%'
AND yr = 2017 AND votes >= ALL(SELECT votes FROM ge y WHERE x.constituency = y. constituency AND y.yr = 2017)
GROUP BY party

---

Thank you for your hard work.

select party,count(party) from
(
select constituency,party ,votes,
rank() over (partition by constituency order by votes desc) as posn
from ge
where constituency like 'S%' and yr = 2017
) b
where posn =1
group by party
order by party

try 5th like this..

select constituency, party from (SELECT constituency, party, votes, RANK() over (partition by constituency order by votes desc) as r
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017) x
where r=1

or try 5th like this

with temp as (select constituency, party, RANK() over(partition by constituency order by votes desc) as RANK from ge
where (constituency between 'S14000021' and 'S14000026') and yr = 2017)

select constituency, party from temp
where RANK = 1