skip to Main Content

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


  1. If you want to include rank of each political part’s vote share in every election year, you can modify your query like this:

    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 extract(year FROM e.e_date) ORDER BY e.vote_share DESC) AS 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.country_name,
        extract(year FROM e.e_date),
        e.party_name,
        e.vote_share
    ORDER BY
        e.country_name,
        year;
    
    Login or Signup to reply.
  2. SELECT * FROM
    
    (SELECT
        e.country_name,
        extract(year FROM e.e_date) AS year,
        e.party_name,
        p.family_name,
        round(e.vote_share, 1) AS vote_share,
        RANK() OVER (PARTITION BY extract(year FROM e.e_date) ORDER BY e.vote_share DESC) AS vote_rank_number
    FROM
        election e
        LEFT JOIN party p ON e.party_id = p.id
    WHERE
        e.e_type = 'parliament'
        AND e.country_name = 'Luxembourg'
        AND e.e_date >= '1980-01-01'::date
        AND e.e_date < '2020-01-01'::date
    ORDER BY
        e.country_name,
        year
    ) 
    
    WHERE family_name IN ('Green/Ecologist')
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search