I have the following tables (simplified) in Postgres:
CREATE TABLE party(
id int PRIMARY KEY,
family_name varchar(50) NOT NULL
);
CREATE TABLE election(
id int,
country_name varchar(50) NOT NULL,
e_type election_type NOT NULL,
e_date date NOT NULL,
vote_share numeric,
seats int,
seats_total int NOT NULL,
party_name_short varchar(10) NOT NULL,
party_name varchar(255) NOT NULL,
party_name_english varchar(255) NOT NULL,
party_id int REFERENCES party(id)
);
I like to know how a certain political party performs in elections. To do so I wrote a simple query:
SELECT
e.country_name,
extract(year FROM e.e_date) AS year,
e.party_name,
round(e.vote_share, 1) AS vote_share
FROM
election e
LEFT JOIN party p ON e.party_id = p.id
WHERE
e.e_type = 'parliament'
AND p.family_name IN ('Green/Ecologist')
AND e.country_name = 'Luxembourg'
AND e.e_date >= '1980-01-01'::date
AND e.e_date < '2020-01-01'::date
GROUP BY
e.country_name,
e.e_date,
e.party_name,
e.vote_share
ORDER BY
e.country_name,
year;
This gives the following result:
+------------+------+-----------------------------------+-----+
| Luxembourg | 1984 | Déi Gréng | 5.2 |
| Luxembourg | 1989 | Déi Gréng | 4.2 |
| Luxembourg | 1989 | Greng Lëscht Ekologesch Initiativ | 4.2 |
+------------+------+-----------------------------------+-----+
I’d like to add one more information: the rank of each political party in election as measured by its vote share. I can do this for a single election (see result below) but my code won’t work for the above written query. Please find below my working query for single election:
SELECT
extract(year FROM e.e_date) AS year,
e.party_name,
e.country_name,
round(e.vote_share, 1) AS vote_share,
RANK() OVER (PARTITION BY e.id ORDER BY e.vote_share DESC) vote_rank_number
FROM
election e
WHERE
e.id = 1;
and the result:
+------+----------------------------------+-------------+------+---+
| 1972 | Labour Party | New Zealand | 48.4 | 1 |
| 1972 | National Party | New Zealand | 41.5 | 2 |
| 1972 | Social Credit | Democratic Party | New Zealand | 6.7 | 3 |
| 1972 | Values Party | New Zealand | 2.0 | 4 |
+------+----------------------------------+-------------+------+---+
I use Postgres 14 but I can install 15 if needed.
UPDATE
Please have a look at the following data. It shows election result for one year only. Déi Gréng
ranks fourth in this election.
+------------+------+-----------------------------------------------+----------------------------------------------+------------+------+
| country | year | party name | party name original | vote_share | rank |
+------------+------+-----------------------------------------------+----------------------------------------------+------------+------+
| Luxembourg | 1984 | Chrëschtlech Sozial Vollekspartei | Christian Social People's Party | 34.9 | 1 |
| Luxembourg | 1984 | Lëtzebuerger Sozialistesch Aarbechterpartei | Luxembourg Socialist Workers' Party | 33.6 | 2 |
| Luxembourg | 1984 | Demokratesch Partei | Democratic Party | 18.7 | 3 |
| Luxembourg | 1984 | Déi Gréng | The Greens | 5.2 | 4 |
| Luxembourg | 1984 | Kommunistesch Partei Lëtzebuerg | Communist Party of Luxembourg | 5.0 | 5 |
| Luxembourg | 1984 | Jean Gremling List – Socialistes Indépendants | Jean Gremling List -- Independent Socialists | 2.5 | 6 |
+------------+------+-----------------------------------------------+----------------------------------------------+------------+------+
No let’s back to my initial query. Here I want to add rank().
SELECT
e.country_name,
extract(year FROM e.e_date) AS year,
e.party_name,
round(e.vote_share, 1) AS vote_share,
RANK() OVER (PARTITION BY e.id ORDER BY e.vote_share DESC) vote_rank_number
FROM
election e
LEFT JOIN party p ON e.party_id = p.id
WHERE
e.e_type = 'parliament'
AND p.family_name IN ('Green/Ecologist')
AND e.country_name = 'Luxembourg'
AND e.e_date >= '1980-01-01'::date
AND e.e_date < '2020-01-01'::date
GROUP BY
e.id,
e.country_name,
e.e_date,
e.party_name,
e.vote_share
ORDER BY
e.country_name,
year;
The query gives the following result:
+------------+------+-----------------------------------+------------+------+
| country | year | party name | vote_share | rank |
+------------+------+-----------------------------------+------------+------+
| Luxembourg | 1984 | Déi Gréng | 5.2 | 1 |
| Luxembourg | 1989 | Greng Lëscht Ekologesch Initiativ | 4.2 | 1 |
| Luxembourg | 1989 | Déi Gréng | 4.2 | 2 |
| Luxembourg | 1994 | Déi Gréng | 10.2 | 1 |
| Luxembourg | 1999 | Greng Lëscht Ekologesch Initiativ | 1.1 | 2 |
| Luxembourg | 1999 | Déi Gréng | 9.1 | 1 |
| Luxembourg | 2004 | Déi Gréng | 11.5 | 1 |
| Luxembourg | 2009 | Déi Gréng | 11.7 | 1 |
| Luxembourg | 2013 | Déi Gréng | 10.1 | 1 |
| Luxembourg | 2018 | Déi Gréng | 14.1 | 1 |
+------------+------+-----------------------------------+------------+------+
It wrongly outputs 1
for 1984
election year. It should be 4
as shown earlier. I hope I’m more clear know.
2
Answers
If you want to include rank of each political part’s vote share in every election year, you can modify your query like this: