9-Window-function.md

Questions Index

• Ques 1

• Ques 2

• Ques 3

• Ques 4

• Ques 5

• Ques 6

Ques 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

Ques 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

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

Ques 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

Ques 5. 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 posn
      FROM ge
      WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
            AND yr  = 2017
     ) ranked_parties
WHERE ranked_parties.posn = 1

Ques 6. Show how many seats for each party in Scotland in 2017. Scottish constituencies start with 'S'.

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
     ) ranked_parties
WHERE ranked_parties.posn = 1
GROUP BY party