Difference between revisions of "Window functions"

From SQLZOO
Jump to navigation Jump to search
Line 65: Line 65:
 
==Edinburgh Constituency==
 
==Edinburgh Constituency==
 
<div class='qu'>
 
<div class='qu'>
 +
Edinburgh constituencies are numbered S14000021 to S14000026.
 
<span class='params respectorder'></span>
 
<span class='params respectorder'></span>
The 2015 election is a different PARTITION to the 2017 election. We only care about the order of votes for each year.
+
<div class='imper'>Use PARTITION BY constituency to show the ranking of each party in Edinburgh in 2017.</div>
<div class='imper'>Use PARTITION to show the ranking of each party in S14000021 in each year.</div>
 
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT yr,party, votes,
 
SELECT yr,party, votes,
 
       RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
 
       RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
 
   FROM ge
 
   FROM ge
  WHERE constituency = 'S14000021'
+
  WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
 
ORDER BY party,yr
 
ORDER BY party,yr
 
</source>
 
</source>
 
<source lang='sql' class='ans'>
 
<source lang='sql' class='ans'>
SELECT yr,party, votes,
+
SELECT constituency,party, votes,
       RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
+
       RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) as posn
 
   FROM ge
 
   FROM ge
 
  WHERE constituency = 'S14000021'
 
  WHERE constituency = 'S14000021'
ORDER BY party,yr
+
ORDER BY posn,constituency
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 12:14, 23 November 2019

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.

Warming up

Show the lastName, party and votes for the constituency 'S14000024' in 2019.

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

Who won?

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 = 'E14000539' AND yr = 2017
ORDER BY votes
SELECT party, votes,
       RANK() OVER (ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party

PARTITION BY

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.
SELECT yr,party, votes,
      RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency = 'S14000021'
ORDER BY party,yr
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

Edinburgh constituencies are numbered S14000021 to S14000026.

Use PARTITION BY constituency to show the ranking of each party in Edinburgh in 2017.
SELECT yr,party, votes,
      RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
ORDER BY party,yr
SELECT constituency,party, votes,
      RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency = 'S14000021'
ORDER BY posn,constituency