How do you find the second highest salary in PostgreSQL, and why can
LIMIT 1 OFFSET 1be wrong?PostgreSQLCREATE 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:
PostgreSQLCREATE 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_RANKworks for any Nth highest, and withPARTITION BY deptfor each department. If salaries can be NULL,ORDER BY ... DESCputs NULLs first in PostgreSQL; addNULLS LAST.How do you get the top N rows per group in PostgreSQL (
DISTINCT ONvsROW_NUMBER)?For the top row per group,
DISTINCT ON (dept)keeps the first row of each group inORDER BYorder; for the top N, number the rows withROW_NUMBER()and filter.PostgreSQLCREATE 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;PostgreSQLCREATE 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 ONis PostgreSQL-only, and itsORDER BYmust start with theDISTINCT ONcolumns. Add a tiebreaker (id), or ties are broken arbitrarily. UseRANK()if tied rows should all be kept.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 columnctid, the row's physical location.PostgreSQLCREATE 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
ctidchanges when a row is updated or the table rewritten, so never store one. Then add aUNIQUEconstraint so duplicates cannot return.What does
SUM(amount) OVER (ORDER BY day)return when two rows share the same day?PostgreSQLCREATE 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 BYand no frame clause, the frame isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, andRANGEincludes every peer row with the sameday. For a total that grows row by row, useROWSand a unique order:PostgreSQLCREATE 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_idto restart the total per account. See window function calls.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_numberstays constant and labels the island.PostgreSQLCREATE 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 - integeris a date, hence the::inton thebigintrow number. TheDISTINCTmatters: the repeated March 2 login would break the arithmetic.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 thanSUM(CASE WHEN ... END).PostgreSQLCREATE 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 | 270andSouth | 80 | 100 | 180. The columns must be known when you write the query; for dynamic ones, thetablefuncextension hascrosstab(), or pivot in the application. A quarter with no rows gives NULL; useCOALESCE(..., 0)for zeros.What does
NOT INreturn in PostgreSQL when the subquery contains a NULL?PostgreSQLCREATE 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)meansid <> 1 AND id <> NULL, andid <> NULLis unknown, so the condition is never true. UseNOT EXISTS, which only asks whether a matching row exists:PostgreSQLCREATE 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 EXISTSas an anti-join, whileNOT INbecomes a hashed subplan at best and a per-row subplan when the result does not fit inwork_mem. More classic traps on the SQL page.How do you calculate a median in PostgreSQL (
percentile_contvspercentile_disc)?PostgreSQLCREATE 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 nomedian(); both are ordered-set aggregates written withWITHIN GROUP (ORDER BY ...).percentile_continterpolates between the two middle values;percentile_discreturns 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.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 withgenerate_seriesandLEFT JOINthe orders to them.PostgreSQLCREATE 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 | 0instead of disappearing.COUNT(o.id)counts matched rows only, whileCOUNT(*)would give 1 for the empty month. Ontimestamptz,date_truncuses the session time zone, or a zone passed as a third argument (PostgreSQL 12+).Why is
OFFSETpagination slow on deep pages, and what is keyset pagination?OFFSET 100000still 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.PostgreSQLCREATE 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, andidmakes the order unique. The cost: no jumping to page 37, only next and previous.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.idso each pair appears once, and compare with the range overlap operator&&(ora.start_at < b.end_at AND b.start_at < a.end_at).PostgreSQLCREATE 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 | 2comes 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 thebtree_gistextension.
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.