SQL & PostgreSQL

Standard SQL plus the PostgreSQL specifics interviewers probe; every snippet below was run on PostgreSQL 16.

Commands & query order

Logical evaluation order (not the written order):

1. WITH, then FROM + JOIN
2. WHERE → GROUP BY → HAVING
3. SELECT list, including window functions (aliases are born here)
4. DISTINCT → UNION / INTERSECT / EXCEPT → ORDER BY → LIMIT / OFFSET

• So WHERE annual > 1000 fails with column "annual" does not exist, and so does HAVING n > 1: repeat the expression. ORDER BY accepts a bare alias or a position (ORDER BY 2), not an alias inside an expression.
• Aggregates and window functions are not allowed in WHERE; filter window results in an outer query or CTE.
• Every selected column must be grouped or aggregated. PostgreSQL relaxes this when you group by the primary key.

Joins

With a(id) = {1,2,3} and b(id) = {2,3,4}:

• Self join: employees e JOIN employees m ON m.id = e.manager_id.
• A filter on the right table in WHERE turns a LEFT JOIN into an inner join; put it in ON.
• One-to-many joins multiply rows, so sum() after them double counts.

Filtering & aggregation

• WHERE filters rows before grouping; HAVING filters groups after it. Put non-aggregate conditions in WHERE.
• count(*) counts rows, count(col) non-NULL values, count(DISTINCT col) distinct non-NULL values.
• Other aggregates ignore NULLs. Over zero rows sum, avg and max return NULL but count returns 0: use coalesce(sum(x), 0).
• UNION removes duplicates (extra sort or hash); UNION ALL keeps them and is cheaper.

SELECT dept_id,
       count(*)                             AS headcount,
       count(*) FILTER (WHERE salary >= 150) AS senior,
       round(avg(salary), 2)                AS avg_salary
FROM employees
WHERE created_at > now() - interval '1 year'
GROUP BY dept_id
HAVING count(*) > 1
ORDER BY headcount DESC;

Subqueries, EXISTS & IN

• A scalar subquery returning two rows fails: more than one row returned by a subquery used as an expression.
• A correlated subquery references the outer row, so it logically runs once per row.
• EXISTS stops at the first match; PostgreSQL usually plans both IN (subquery) and EXISTS as a semi-join.

SELECT 1 NOT IN (2, NULL);                         -- NULL, not true
SELECT id FROM a WHERE id NOT IN (SELECT id FROM b);  -- 0 rows if b.id has a NULL
SELECT id FROM a
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id); -- correct: 1

Gotcha

NOT IN against a subquery that returns even one NULL yields no rows at all. Use NOT EXISTS for anti-joins.

CTEs & recursive CTEs

• WITH x AS (…) names a subquery; later CTEs can use earlier ones.
• Since PG 12, a non-recursive, side-effect-free CTE used once is inlined. AS MATERIALIZED forces one evaluation (an optimization fence); AS NOT MATERIALIZED forces inlining.
• Recursive CTE = anchor UNION ALL recursive term; it stops when the recursive term returns no rows. UNION drops duplicate rows, which can stop cycles; PG 14 added CYCLE and SEARCH clauses.
• CTEs can modify data: WITH moved AS (DELETE … RETURNING *) INSERT INTO archive SELECT * FROM moved;

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL             -- anchor
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e JOIN chain c ON e.manager_id = c.id -- step
)
SELECT name, depth FROM chain ORDER BY depth, name;

Window functions

fn() OVER (PARTITION BY … ORDER BY … frame) computes across related rows without collapsing them. Results for salaries 200, 150, 150, 130 DESC:

• With ORDER BY the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so tied rows (peers) share a running total. Use ROWS for strictly row by row.
• Without ORDER BY the frame is the whole partition. With the default frame, last_value() returns the current row (or its last peer).
• Reuse a definition: WINDOW w AS (ORDER BY salary DESC), then rank() OVER w.

SELECT day, amount,
  sum(amount) OVER (ORDER BY day)                         AS running_total,
  avg(amount) OVER (ORDER BY day
                    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
  amount - lag(amount) OVER (ORDER BY day)                AS delta,
  round(100.0 * amount / sum(amount) OVER (), 1)          AS pct_of_total
FROM daily_revenue;

Classic interview queries

-- Nth highest distinct salary (N = 2): OFFSET N-1
SELECT DISTINCT salary FROM employees WHERE salary IS NOT NULL
ORDER BY salary DESC OFFSET 1 LIMIT 1;
-- Second highest, NULL when there is none
SELECT max(salary) FROM employees
WHERE salary < (SELECT max(salary) FROM employees);
-- Duplicates, then delete them keeping the lowest id
SELECT email, count(*) FROM employees GROUP BY email HAVING count(*) > 1;
DELETE FROM employees a USING employees b
WHERE a.email = b.email AND a.id > b.id;

-- Top 2 earners per department
SELECT dept_id, name, salary FROM (
  SELECT *, row_number() OVER (PARTITION BY dept_id
                               ORDER BY salary DESC NULLS LAST) AS rn
  FROM employees) t
WHERE rn <= 2;
-- Top 1 per group, PostgreSQL only
SELECT DISTINCT ON (dept_id) dept_id, name, salary
FROM employees ORDER BY dept_id, salary DESC NULLS LAST;

• Earns more than their manager: self join plus WHERE e.salary > m.salary. Departments with no employees: the anti-join in Joins.

Keys, constraints & normal forms

• PRIMARY KEY = UNIQUE + NOT NULL, one per table, backed by a unique B-tree index. Surrogate keys (id) carry no business meaning; natural keys (email) do.
• Prefer id bigint GENERATED ALWAYS AS IDENTITY to serial: it is standard SQL and rejects explicit ids unless you write OVERRIDING SYSTEM VALUE.
• REFERENCES t(id) ON DELETE CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION (default NO ACTION).
• PostgreSQL does not index the referencing (child) column of a foreign key; add one for joins and fast parent deletes.
• UNIQUE allows many NULLs (NULLs are distinct); PG 15 added UNIQUE NULLS NOT DISTINCT.
• Also CHECK (salary > 0), DEFAULT, and EXCLUDE USING gist (room WITH =, during WITH &&) against overlapping bookings (= on an integer needs the btree_gist extension).

• Each form includes the previous ones. OLTP schemas aim for 3NF and denormalize deliberately (materialized views, counters, analytics star schemas).

Indexes

• Leftmost prefix: (a, b, c) serves a, a, b and a, b, c. A filter on b alone cannot seek; at best it scans the whole index (PG 18 added B-tree skip scan, which helps when a has few distinct values).
• Equality columns first, then the range or sort column: (user_id, created_at) serves WHERE user_id = ? AND created_at > ? and WHERE user_id = ? ORDER BY created_at DESC LIMIT 10 with no sort. Columns after the first range condition cannot narrow the seek.
• Every index slows writes; unused ones show idx_scan = 0 in pg_stat_user_indexes.
• Plain CREATE INDEX takes a SHARE lock that blocks writes. CREATE INDEX CONCURRENTLY does not, but is slower, cannot run inside a transaction block, and leaves an INVALID index if it fails.

Reading EXPLAIN

• EXPLAIN shows the estimated plan; EXPLAIN ANALYZE runs the query (wrap writes in BEGIN … ROLLBACK). BUFFERS adds page I/O.
• cost=startup..total is in planner units; actual time is ms per loop, times loops.

Troubleshooting a slow query:

1. Estimated vs actual rows far apart → stale statistics → ANALYZE t.
2. Seq Scan on a selective filter → missing index, a function or cast on the column (date(created_at) = …), or a type mismatch.
3. Large Rows Removed by Filter → the index does not cover the predicate.
4. Sort Method: external merge Disk → raise work_mem (default 4MB) or index the sort key.

Transactions & isolation

• Atomic: all or nothing. Consistent: constraints hold. Isolated: no seeing others’ partial work. Durable: commits survive a crash (write-ahead log).
• Each statement autocommits unless you BEGIN. After an error, every command fails with current transaction is aborted until ROLLBACK (or ROLLBACK TO SAVEPOINT).
• BEGIN ISOLATION LEVEL SERIALIZABLE; sets the level; SET TRANSACTION must precede the first query.

• Dirty read: seeing uncommitted data. Non-repeatable: a re-read row changed. Phantom: a re-run predicate returns new rows. Write skew: two transactions read the same rows, update different ones and break an invariant (both on-call doctors go off call).
• Read committed takes a snapshot per statement. An UPDATE that waited on a locked row re-checks its WHERE on the new version, so SET qty = qty - 1 is safe; read-then-write in application code can still lose updates.
• Repeatable read keeps one snapshot per transaction; updating a row another transaction changed fails with could not serialize access due to concurrent update (SQLSTATE 40001).
• Serializable (SSI) also aborts dependency cycles such as write skew, again with 40001.

Interview tip

At repeatable read and serializable, 40001 is expected, not a bug: retry the whole transaction.

MVCC, VACUUM & locking

• MVCC: each row version has xmin (creating transaction) and xmax (deleting one). UPDATE writes a new version and marks the old one dead, so readers and writers never block each other.
• VACUUM makes dead rows’ space reusable (the file only shrinks by empty trailing pages), updates the visibility map for index-only scans, and freezes old transaction IDs to prevent wraparound. Long-running or idle in transaction sessions block this cleanup and cause bloat.
• Autovacuum is on by default and vacuums a table once dead rows exceed 50 + 20% of its rows. VACUUM FULL rewrites the table and returns space to the OS, under an ACCESS EXCLUSIVE lock.
• Row locks, strongest first: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. NOWAIT errors instead of waiting; SKIP LOCKED skips locked rows.
• Table locks: SELECT takes ACCESS SHARE, writes ROW EXCLUSIVE; ALTER TABLE, DROP, TRUNCATE and VACUUM FULL take ACCESS EXCLUSIVE, which blocks reads too. Set lock_timeout before DDL on busy tables.
• Deadlocks are checked after deadlock_timeout (default 1s); one transaction fails with deadlock detected (40P01). Lock rows in a consistent order and keep transactions short.
• Optimistic locking: UPDATE … SET version = version + 1 WHERE id = $1 AND version = $2, then check the row count. Pessimistic: SELECT … FOR UPDATE.

WITH next AS (                      -- job queue: workers never double-pick
  SELECT id FROM jobs WHERE status = 'queued'
  ORDER BY id LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running'
FROM next WHERE jobs.id = next.id
RETURNING jobs.id;

UPSERT, JSONB & views

INSERT INTO inventory (sku, qty) VALUES ('A1', 5)
ON CONFLICT (sku) DO UPDATE
  SET qty = inventory.qty + EXCLUDED.qty, updated_at = now()
RETURNING sku, qty;
-- or: ON CONFLICT (sku) DO NOTHING

• ON CONFLICT needs a unique index or constraint on the target columns; EXCLUDED is the proposed row. MERGE (PG 15+) is the standard alternative.

• Cast before comparing: (doc ->> 'age')::int > 18. Also jsonb_set, jsonb_agg, jsonb_array_elements, and subscripts doc['addr']['city'] (PG 14+).
• json stores the text as is; jsonb is parsed binary, keeps the last duplicate key, and is indexable. Default to jsonb.
• GIN with the default jsonb_ops supports ?, ?|, ?&, @>, @?, @@; jsonb_path_ops only @>, @?, @@, but is smaller and faster.

• REFRESH MATERIALIZED VIEW CONCURRENTLY keeps it readable during the refresh but needs a unique index on it.

Pagination & psql

• OFFSET n still reads and discards n rows (page 5,001 of 20 reads 100,020 index entries), and rows shift between pages as data arrives.
• Keyset (seek) pagination filters on the last row’s sort key plus a unique tiebreaker: same cost for every page, but no jumping to page N.

-- index: (created_at, id); values come from the previous page's last row
SELECT id, created_at FROM events
WHERE (created_at, id) < ('2026-09-28 10:00+00', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Quick answers

• DELETE vs TRUNCATE vs DROP? DELETE removes chosen rows one by one (row triggers, dead rows); TRUNCATE empties the table at once (RESTART IDENTITY resets ids); DROP removes the table. All can be rolled back in PostgreSQL.
• Primary key vs unique? One PK, never NULL; many unique constraints, NULLs allowed.
• Clustered index? PostgreSQL tables are heaps; CLUSTER reorders a table once and is not maintained.
• Why isn’t my index used? Low selectivity, a function or cast on the column, a missing leading column, stale statistics, or a tiny table.
• text vs varchar(n) vs char(n)? Same speed in PostgreSQL; varchar(n) only for a real limit; char(n) pads with spaces.
• timestamp vs timestamptz? timestamptz stores an absolute instant shown in the session time zone; use it for events.
• Function vs procedure? Functions return a value inside the caller’s transaction; procedures (PG 11+) run with CALL and may COMMIT.
• Partitioning? Declarative PARTITION BY RANGE | LIST | HASH; the planner prunes partitions, and old data goes by DETACH or DROP, not DELETE.
• Scaling reads? Indexes, connection pooling (each connection is a process; max_connections is 100 by default), caching, replicas.
• Fast row count? count(*) scans the table (MVCC keeps no stored count); pg_class.reltuples is an estimate.

Gotchas & traps

• NULL = NULL is NULL, not true: use IS NULL or IS DISTINCT FROM.
• NULLs sort as larger than any value, so ORDER BY salary DESC LIMIT 1 returns a NULL first. Add NULLS LAST or filter.
• 'a' || NULL is NULL, but concat('a', NULL) is 'a'.
• 5 / 2 is 2 (integer division); write 5 / 2.0 or cast.
• Without ORDER BY, row order is undefined, even with LIMIT.
• BETWEEN includes both ends; for time ranges use >= start AND < end.
• Unquoted identifiers fold to lowercase, so "User" needs quotes forever. Strings take single quotes.
• now() is frozen at transaction start; clock_timestamp() is the wall clock.
• LIKE is case-sensitive (ILIKE is not), and a leading % cannot use a B-tree.
• Identity values have gaps: rolled-back inserts still consume them.