Difference between revisions of "Window functions"

From SQLZOO
Jump to navigation Jump to search
Line 29: Line 29:
 
If you RANK using (ORDER BY votes DESC) then the candidate with the most votes has rank 1.
 
If you RANK using (ORDER BY votes DESC) then the candidate with the most votes has rank 1.
 
<div class='imper'>Show the party and RANK for constituency S14000024 in 2017. List the output by party</div>
 
<div class='imper'>Show the party and RANK for constituency S14000024 in 2017. List the output by party</div>
 +
<source class='setup'>
 +
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
 +
</source>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT party, votes,
 
SELECT party, votes,

Revision as of 21:20, 6 January 2020

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

yrfirstNamelastNameconstituencypartyvotes
2015IanMurrayS14000024Labour19293
2015NeilHayS14000024Scottish National Party16656
2015MilesBriggsS14000024Conservative8626
2015PhylMeyerS14000024Green2090
2015PramodSubbaramanS14000024Liberal Democrat1823
2015PaulMarshallS14000024UK Independence Party601
2015ColinFoxS14000024Scottish Socialist Party197
2017IanMURRAYS14000024Labour26269
2017JimEADIES14000024SNP10755
2017Stephanie Jane HarleySMITHS14000024Conservative9428
2017Alan ChristopherBEALS14000024Liberal Democrats1388

Warming up

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

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
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
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. Order your results so the winners are shown first, then ordered by constituency.
SELECT constituency,party, votes
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
ORDER BY constituency,votes DESC
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

Winners Only

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, votes
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
ORDER BY constituency,votes DESC
SELECT constituency, 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
   ) AS ed
WHERE posn = 1

Scottish seats

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 constituency,party, votes
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
ORDER BY constituency,votes DESC
SELECT 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
   ) AS ed
WHERE posn = 1
GROUP BY party