Menu
CoddyTech

PostgreSQL Interview Questions for Experienced Developers

Classic query problems solved in PostgreSQL: ranking, duplicates, running totals, streaks, pivots, the NOT IN trap, medians, reports and pagination.

11 questions
  1. How do you find the second highest salary in PostgreSQL, and why can LIMIT 1 OFFSET 1 be wrong?

    PostgreSQL
    CREATE TABLE employees (id int PRIMARY KEY, name text, dept text, salary int);
    INSERT INTO employees VALUES
      (1, 'Asha', 'Eng', 120000), (2, 'Ravi', 'Eng', 90000),
      (3, 'Meera', 'Eng', 120000), (4, 'John', 'Sales', 70000),
      (5, 'Priya', 'Sales', 82000), (6, 'Karan', 'HR', 60000);
    SELECT salary FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1;

    It returns 120000. Two people share the top salary, so the second row still holds the highest value. Ask whether the interviewer wants the second distinct value, then rank:

    PostgreSQL
    CREATE TABLE employees (id int PRIMARY KEY, name text, dept text, salary int);
    INSERT INTO employees VALUES
      (1, 'Asha', 'Eng', 120000), (2, 'Ravi', 'Eng', 90000),
      (3, 'Meera', 'Eng', 120000), (4, 'John', 'Sales', 70000),
      (5, 'Priya', 'Sales', 82000), (6, 'Karan', 'HR', 60000);
    SELECT DISTINCT salary FROM (
      SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
      FROM employees
    ) ranked
    WHERE rnk = 2;

    That returns 90000. DENSE_RANK works for any Nth highest, and with PARTITION BY dept for each department. If salaries can be NULL, ORDER BY ... DESC puts NULLs first in PostgreSQL; add NULLS LAST.

  2. How do you get the top N rows per group in PostgreSQL (DISTINCT ON vs ROW_NUMBER)?

    For the top row per group, DISTINCT ON (dept) keeps the first row of each group in ORDER BY order; for the top N, number the rows with ROW_NUMBER() and filter.

    PostgreSQL
    CREATE TABLE employees (id int PRIMARY KEY, name text, dept text, salary int);
    INSERT INTO employees VALUES
      (1, 'Asha', 'Eng', 120000), (2, 'Ravi', 'Eng', 90000),
      (3, 'Meera', 'Eng', 120000), (4, 'John', 'Sales', 70000),
      (5, 'Priya', 'Sales', 82000), (6, 'Karan', 'HR', 60000);
    SELECT DISTINCT ON (dept) dept, name, salary
    FROM employees
    ORDER BY dept, salary DESC, id;
    PostgreSQL
    CREATE TABLE employees (id int PRIMARY KEY, name text, dept text, salary int);
    INSERT INTO employees VALUES
      (1, 'Asha', 'Eng', 120000), (2, 'Ravi', 'Eng', 90000),
      (3, 'Meera', 'Eng', 120000), (4, 'John', 'Sales', 70000),
      (5, 'Priya', 'Sales', 82000), (6, 'Karan', 'HR', 60000);
    SELECT dept, name, salary FROM (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn
      FROM employees
    ) t
    WHERE rn <= 2
    ORDER BY dept, rn;

    DISTINCT ON is PostgreSQL-only, and its ORDER BY must start with the DISTINCT ON columns. Add a tiebreaker (id), or ties are broken arbitrarily. Use RANK() if tied rows should all be kept.

  3. How do you delete duplicate rows in PostgreSQL and keep one of each?

    Number the rows within each group of duplicates and delete every row numbered above 1. With a primary key, keep the lowest id; without one, use the system column ctid, the row's physical location.

    PostgreSQL
    CREATE TABLE contacts (email text, name text);
    INSERT INTO contacts VALUES
      ('a@x.com', 'Asha'), ('a@x.com', 'Asha'), ('r@x.com', 'Ravi'),
      ('a@x.com', 'Asha'), ('m@x.com', 'Meera');
    DELETE FROM contacts
    WHERE ctid IN (
      SELECT ctid FROM (
        SELECT ctid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY ctid) AS rn
        FROM contacts
      ) d
      WHERE rn > 1
    );
    SELECT email, name FROM contacts ORDER BY email;

    Three rows remain, one per email. A ctid changes when a row is updated or the table rewritten, so never store one. Then add a UNIQUE constraint so duplicates cannot return.

  4. What does SUM(amount) OVER (ORDER BY day) return when two rows share the same day?

    PostgreSQL
    CREATE TABLE sales (day date, amount int);
    INSERT INTO sales VALUES
      ('2026-01-01', 100), ('2026-01-02', 50), ('2026-01-02', 30), ('2026-01-03', 20);
    SELECT amount, SUM(amount) OVER (ORDER BY day) AS running
    FROM sales
    ORDER BY day, amount;

    It returns 180 for both January 2 rows. With ORDER BY and no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes every peer row with the same day. For a total that grows row by row, use ROWS and a unique order:

    PostgreSQL
    CREATE TABLE sales (day date, amount int);
    INSERT INTO sales VALUES
      ('2026-01-01', 100), ('2026-01-02', 50), ('2026-01-02', 30), ('2026-01-03', 20);
    SELECT day, amount,
      SUM(amount) OVER (ORDER BY day, amount
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
    FROM sales
    ORDER BY day, amount;

    Now it climbs 100, 130, 180, 200. Add PARTITION BY account_id to restart the total per account. See window function calls.

  5. How do you find streaks of consecutive days in PostgreSQL (gaps and islands)?

    Subtract a row number from each date: inside a run of consecutive days, the date and the row number grow together, so day - row_number stays constant and labels the island.

    PostgreSQL
    CREATE TABLE logins (user_id int, day date);
    INSERT INTO logins VALUES
      (1, '2026-03-01'), (1, '2026-03-02'), (1, '2026-03-02'), (1, '2026-03-03'),
      (1, '2026-03-05'), (1, '2026-03-06'),
      (2, '2026-03-01'), (2, '2026-03-03');
    SELECT user_id, min(day) AS streak_start, max(day) AS streak_end, count(*) AS days
    FROM (
      SELECT user_id, day,
             day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
      FROM (SELECT DISTINCT user_id, day FROM logins) d
    ) t
    GROUP BY user_id, grp
    ORDER BY user_id, streak_start;

    User 1 has a 3-day and a 2-day streak, user 2 two single days. date - integer is a date, hence the ::int on the bigint row number. The DISTINCT matters: the repeated March 2 login would break the arithmetic.

  6. How do you pivot rows into columns in PostgreSQL?

    Use conditional aggregation: one aggregate per output column, each with a FILTER (WHERE ...) clause. It is standard SQL and reads better than SUM(CASE WHEN ... END).

    PostgreSQL
    CREATE TABLE sales (region text, quarter text, amount int);
    INSERT INTO sales VALUES
      ('North', 'Q1', 100), ('North', 'Q2', 150), ('South', 'Q1', 80),
      ('South', 'Q2', 60), ('South', 'Q2', 40), ('North', 'Q1', 20);
    SELECT region,
      SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
      SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2,
      SUM(amount) AS total
    FROM sales
    GROUP BY region
    ORDER BY region;

    It returns North | 120 | 150 | 270 and South | 80 | 100 | 180. The columns must be known when you write the query; for dynamic ones, the tablefunc extension has crosstab(), or pivot in the application. A quarter with no rows gives NULL; use COALESCE(..., 0) for zeros.

  7. What does NOT IN return in PostgreSQL when the subquery contains a NULL?

    PostgreSQL
    CREATE TABLE customers (id int, name text);
    INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
    CREATE TABLE orders (id int, customer_id int);
    INSERT INTO orders VALUES (1, 1), (2, NULL);
    SELECT count(*) FROM customers
    WHERE id NOT IN (SELECT customer_id FROM orders);

    It returns 0. id NOT IN (1, NULL) means id <> 1 AND id <> NULL, and id <> NULL is unknown, so the condition is never true. Use NOT EXISTS, which only asks whether a matching row exists:

    PostgreSQL
    CREATE TABLE customers (id int, name text);
    INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
    CREATE TABLE orders (id int, customer_id int);
    INSERT INTO orders VALUES (1, 1), (2, NULL);
    SELECT count(*) FROM customers c
    WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

    That returns 2. It is also faster: PostgreSQL plans NOT EXISTS as an anti-join, while NOT IN becomes a hashed subplan at best and a per-row subplan when the result does not fit in work_mem. More classic traps on the SQL page.

  8. How do you calculate a median in PostgreSQL (percentile_cont vs percentile_disc)?

    PostgreSQL
    CREATE TABLE scores (v int);
    INSERT INTO scores VALUES (1), (2), (3), (10);
    SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY v) AS cont,
           percentile_disc(0.5) WITHIN GROUP (ORDER BY v) AS disc
    FROM scores;

    It returns 2.5 | 2. PostgreSQL has no median(); both are ordered-set aggregates written with WITHIN GROUP (ORDER BY ...). percentile_cont interpolates between the two middle values; percentile_disc returns the first actual value that reaches the fraction, so it is always a value from the data. Pass an array, percentile_cont(ARRAY[0.25, 0.5, 0.75]), for several at once. See aggregate functions.

  9. How do you write a monthly revenue report in PostgreSQL that includes months with no sales?

    Group by date_trunc('month', ...), and to keep empty months, generate the months with generate_series and LEFT JOIN the orders to them.

    PostgreSQL
    CREATE TABLE orders (id int, ordered_at timestamp, amount numeric);
    INSERT INTO orders VALUES
      (1, '2026-01-05 10:00', 120), (2, '2026-01-20 18:30', 80),
      (3, '2026-03-02 09:15', 200), (4, '2026-03-31 23:59', 50);
    SELECT to_char(m, 'YYYY-MM') AS month,
           COALESCE(SUM(o.amount), 0) AS revenue,
           COUNT(o.id) AS orders
    FROM generate_series('2026-01-01'::timestamp, '2026-03-01', interval '1 month') AS m
    LEFT JOIN orders o ON date_trunc('month', o.ordered_at) = m
    GROUP BY m
    ORDER BY m;

    February shows 0 | 0 instead of disappearing. COUNT(o.id) counts matched rows only, while COUNT(*) would give 1 for the empty month. On timestamptz, date_trunc uses the session time zone, or a zone passed as a third argument (PostgreSQL 12+).

  10. Why is OFFSET pagination slow on deep pages, and what is keyset pagination?

    OFFSET 100000 still reads and discards 100,000 rows, so deep pages get slower, and inserts between requests shift pages (repeats or skips). Keyset pagination remembers the last row's sort key and asks for the rows after it, straight from an index.

    PostgreSQL
    CREATE TABLE posts (id int PRIMARY KEY, created_at timestamp NOT NULL);
    INSERT INTO posts
    SELECT g, '2026-01-01'::timestamp + (g / 3) * interval '1 hour'
    FROM generate_series(1, 20) g;
    CREATE INDEX ON posts (created_at DESC, id DESC);
    -- the previous page ended with created_at 04:00, id 13
    SELECT id, created_at FROM posts
    WHERE (created_at, id) < ('2026-01-01 04:00', 13)
    ORDER BY created_at DESC, id DESC
    LIMIT 5;

    It returns ids 12, 11, 10, 9, 8. The row comparison handles ties on created_at, and id makes the order unique. The cost: no jumping to page 37, only next and previous.

  11. How do you find overlapping bookings or date ranges in PostgreSQL?

    Two ranges overlap when each starts before the other ends. Self-join on the same room with a.id < b.id so each pair appears once, and compare with the range overlap operator && (or a.start_at < b.end_at AND b.start_at < a.end_at).

    PostgreSQL
    CREATE TABLE bookings (id int, room text, start_at timestamp, end_at timestamp);
    INSERT INTO bookings VALUES
      (1, 'A', '2026-05-01 09:00', '2026-05-01 10:00'),
      (2, 'A', '2026-05-01 09:30', '2026-05-01 11:00'),
      (3, 'A', '2026-05-01 11:00', '2026-05-01 12:00'),
      (4, 'B', '2026-05-01 09:00', '2026-05-01 10:00');
    SELECT a.id AS first, b.id AS second
    FROM bookings a
    JOIN bookings b ON a.room = b.room AND a.id < b.id
     AND tsrange(a.start_at, a.end_at) && tsrange(b.start_at, b.end_at)
    ORDER BY 1, 2;

    Only 1 | 2 comes back. Bookings 2 and 3 touch at 11:00 without overlapping, because a range includes its start and excludes its end ([)) by default. To reject overlaps on insert, add an exclusion constraint: EXCLUDE USING gist (room WITH =, tsrange(start_at, end_at) WITH &&), which needs the btree_gist extension.

Coddy programming languages illustration

Learn to code with Coddy

Get started