SQL & Databases Interview Questions and Answers

An INNER JOIN returns only the rows that have a match on both sides. A LEFT JOIN returns every row from the left table and fills the right table's columns with NULL when there is no match. Choose inner when an unmatched row is irrelevant, and left when you must not lose the left-hand rows, such as listing every library member including those who never borrowed a book.

A common trap is filtering the right table in the WHERE clause, for example WHERE l.book = 'Dune'. NULL never equals anything, so the unmatched rows vanish and the LEFT JOIN quietly behaves like an inner join. Put that condition in the ON clause if you want to keep them.

CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, member_id INTEGER, book TEXT);
INSERT INTO member VALUES (1,'Asha'),(2,'Bruno'),(3,'Chen');
INSERT INTO loan VALUES (10,1,'Dune'),(11,1,'Emma'),(12,2,'Ulysses');

SELECT m.name, l.book FROM member m INNER JOIN loan l ON l.member_id = m.id;

SELECT m.name, l.book FROM member m LEFT JOIN loan l ON l.member_id = m.id;

name   book
-----  -------
Asha   Dune
Asha   Emma
Bruno  Ulysses

name   book
-----  -------
Asha   Dune
Asha   Emma
Bruno  Ulysses
Chen   NULL

What the interviewer is checking: Whether you can explain join semantics in terms of which rows survive, and know the WHERE-versus-ON pitfall.

Likely follow-up: If both tables contain the join key twice, how many rows does the join return for that key?

WHERE filters individual rows before any grouping happens, so it cannot see aggregate results. HAVING filters whole groups after GROUP BY has aggregated them, so it can use things like COUNT(*) or SUM(x).

For example, with parcels in lockers, WHERE drops collected parcels first, then HAVING keeps only lockers with at least two parcels still waiting. As a rule, push every condition that does not need an aggregate into WHERE: fewer rows reach the grouping step, and the intent is clearer to the next reader.

What the interviewer is checking: That you understand the order in which rows are filtered and grouped, not just the keywords.

Likely follow-up: Can you use HAVING without a GROUP BY? What does it mean then?

NULL means unknown, not zero or empty, so SQL uses three-valued logic: true, false or unknown. Any comparison with NULL, including NULL = NULL and 1 <> NULL, evaluates to unknown, and WHERE keeps only rows where the condition is true. That is why you must write IS NULL or IS NOT NULL.

The consequences bite in practice. temp <> 20 silently skips rows where temp is NULL, aggregates such as AVG ignore NULLs, and NOT IN against a list containing a NULL returns nothing. Use IS NULL checks, COALESCE, or NOT EXISTS deliberately rather than hoping.

SELECT NULL = NULL  AS eq,
       NULL IS NULL AS is_null,
       1 <> NULL    AS ne,
       NULL OR 1    AS or_true,
       NULL AND 0   AS and_false;

CREATE TABLE reading(sensor TEXT, temp INTEGER);
INSERT INTO reading VALUES ('s1', 20), ('s2', NULL), ('s3', 30);

SELECT COUNT(*) AS n FROM reading WHERE temp <> 20;
SELECT COUNT(*) AS n FROM reading WHERE temp <> 20 OR temp IS NULL;
SELECT AVG(temp) AS avg_raw, AVG(COALESCE(temp, 0)) AS avg_zero FROM reading;

eq    is_null  ne    or_true  and_false
----  -------  ----  -------  ---------
NULL  1        NULL  1        0

n
-
1

n
-
2

avg_raw  avg_zero
-------  ----------------
25.0     16.6666666666667

What the interviewer is checking: Whether you know three-valued logic and can predict the surprising results it causes.

Likely follow-up: Which is the right average for a sensor that failed to report: ignoring the NULL or treating it as zero?

GROUP BY collapses rows that share the same values in the listed columns into one row per group, so aggregate functions like COUNT, SUM or MAX are computed per group instead of over the whole table. Every selected column must either be in the GROUP BY or be inside an aggregate.

COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL, and COUNT(DISTINCT column) counts the different non-NULL values. For example, a locker with three parcels where only one has a pickup time gives COUNT(*) of 3 but COUNT(picked_up_at) of 1.

What the interviewer is checking: Basic aggregation fluency, plus awareness that aggregates treat NULL specially.

Likely follow-up: Why does selecting a non-grouped, non-aggregated column fail in most databases?

A primary key uniquely identifies each row and cannot be NULL; a table has exactly one. A unique constraint also prevents duplicates, but a table can have several, and many engines allow NULLs in them. A foreign key is a column that must match an existing key in another table, which enforces referential integrity: you cannot create an invoice for a wallet that does not exist.

Foreign keys also decide what happens on delete or update through options like CASCADE, SET NULL or RESTRICT. Remember that most engines do not index the foreign key column automatically, so add an index if you join or delete on it often.

What the interviewer is checking: Whether you understand constraints as integrity guarantees enforced by the database rather than by application code.

Likely follow-up: When would you choose a natural key over a surrogate key like an auto-increment ID?

DELETE removes rows you select with a WHERE clause, one row at a time, fires row-level triggers and can be rolled back. TRUNCATE removes every row in one go, takes no WHERE, is usually much faster, and often resets auto-increment counters. DROP removes the table itself: structure, indexes and data.

Details vary by engine. TRUNCATE is treated as DDL, and in MySQL and Oracle it commits implicitly, while PostgreSQL and SQL Server let you roll it back inside a transaction. It is also often blocked when other tables reference the table through foreign keys. Say "it depends on the database" and name the one you use.

What the interviewer is checking: Whether you can separate data operations from structure operations and flag engine differences honestly.

Likely follow-up: You need to empty a 200-million-row table that is still being read. What would you do instead of one big DELETE?

For simply removing duplicate rows they give the same result, and many optimizers plan them identically. DISTINCT says "give me unique rows". GROUP BY says "form groups", and that only becomes useful once you add aggregates such as COUNT or SUM per group.

So use DISTINCT when you want unique combinations and nothing else, and GROUP BY when you need numbers per group. Do not reach for DISTINCT to hide a join that is multiplying rows; fix the join instead, or you will mask a bug and pay for a sort.

What the interviewer is checking: Whether you pick the right tool for intent and recognise DISTINCT as a code smell when used to patch a bad join.

Likely follow-up: How does COUNT(DISTINCT x) differ from COUNT(x), and what does it cost on a large table?

Both stack the results of two SELECTs that have the same number of columns with compatible types. UNION also removes duplicate rows from the combined result, which requires a sort or hash step. UNION ALL keeps everything and is therefore cheaper.

Default to UNION ALL when you know the two sets cannot overlap, or when duplicates are meaningful, for example combining this year's and last year's transactions. Use UNION only when you really need deduplication. Also note that the column names come from the first SELECT, and an ORDER BY applies to the final combined result.

What the interviewer is checking: Awareness that deduplication has a cost and that the cheaper operator is the right default.

Likely follow-up: How do INTERSECT and EXCEPT relate to UNION, and which databases support them?

This is an anti-join, and there are three standard forms: LEFT JOIN ... WHERE right.id IS NULL, NOT EXISTS (correlated subquery), and NOT IN (subquery). The first two are safe and optimizers handle them well.

NOT IN is the trap. If the subquery returns even one NULL, id NOT IN (...) evaluates to unknown for every row, so the whole result is empty. In the demo, adding a single enrolment with a NULL student_id makes the query return nothing, with no error. Prefer NOT EXISTS, or filter NULLs out of the subquery explicitly.

CREATE TABLE student(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE enrolment(id INTEGER PRIMARY KEY, student_id INTEGER, course TEXT);
INSERT INTO student VALUES (1,'Tara'),(2,'Uma'),(3,'Vik');
INSERT INTO enrolment VALUES (1,1,'Math'),(2,1,'Art'),(3,2,'Math');

-- 1. LEFT JOIN + IS NULL
SELECT s.name FROM student s
LEFT JOIN enrolment e ON e.student_id = s.id WHERE e.id IS NULL;

-- 2. NOT EXISTS
SELECT name FROM student s
WHERE NOT EXISTS (SELECT 1 FROM enrolment e WHERE e.student_id = s.id);

-- 3. NOT IN works... until one student_id is NULL
SELECT name FROM student WHERE id NOT IN (SELECT student_id FROM enrolment);
INSERT INTO enrolment VALUES (4, NULL, 'Chess');
SELECT name FROM student WHERE id NOT IN (SELECT student_id FROM enrolment);
-- this last query returns no rows at all

name
----
Vik

name
----
Vik

name
----
Vik

What the interviewer is checking: Practical SQL idioms and whether you have been burned by NULL semantics in subqueries.

Likely follow-up: Which of the three forms would you pick on a very large table, and how would you confirm with EXPLAIN?

To find them, group by the columns that define a duplicate and keep groups with HAVING COUNT(*) > 1. To delete safely, number the rows inside each duplicate group with ROW_NUMBER() OVER (PARTITION BY email ORDER BY created) and delete everything with a number above 1. That keeps the earliest row and makes your rule explicit.

An older version is DELETE ... WHERE id NOT IN (SELECT MIN(id) ... GROUP BY ...). After cleaning, add a UNIQUE constraint so the duplicates cannot come back, and run the delete inside a transaction after checking the row count with a SELECT first.

CREATE TABLE signup(id INTEGER PRIMARY KEY, email TEXT, created TEXT);
INSERT INTO signup VALUES
 (1,'a@x.io','2024-01-01'),(2,'b@x.io','2024-01-02'),(3,'a@x.io','2024-02-01'),
 (4,'c@x.io','2024-02-02'),(5,'a@x.io','2024-03-01');

-- which emails are duplicated, and how often?
SELECT email, COUNT(*) AS n FROM signup GROUP BY email HAVING COUNT(*) > 1;

-- the extra rows (everything except the earliest per email)
SELECT id, email FROM (
  SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created) AS rn
  FROM signup
) WHERE rn > 1;

-- delete them, keep the earliest
DELETE FROM signup WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created) AS rn FROM signup
  ) WHERE rn > 1);
SELECT id, email FROM signup ORDER BY id;

email   n
------  -
a@x.io  3

id  email
--  ------
3   a@x.io
5   a@x.io

id  email
--  ------
1   a@x.io
2   b@x.io
4   c@x.io

What the interviewer is checking: Whether you can express a deduplication rule precisely and do it safely, not just spot duplicates.

Likely follow-up: How would you stop duplicates differing only by letter case or trailing spaces from sneaking in?

First clarify what "second highest" means: the second distinct value, or the second row? ORDER BY marks DESC LIMIT 1 OFFSET 1 gives the second row, so with two students on 90 it returns 90 again. For the second distinct value use SELECT DISTINCT marks ... OFFSET 1, or MAX(marks) where marks is below the overall maximum.

To generalise to the Nth highest and still return the names, use DENSE_RANK() OVER (ORDER BY marks DESC) and filter on the rank. Also decide what should happen when there is no second value: the MAX form returns NULL, while LIMIT/OFFSET returns no row.

CREATE TABLE score(student TEXT, marks INTEGER);
INSERT INTO score VALUES ('Ana',90),('Ben',85),('Cy',90),('Di',70);

-- wrong when the top mark is tied: returns the top mark again
SELECT marks FROM score ORDER BY marks DESC LIMIT 1 OFFSET 1;

-- second highest DISTINCT value
SELECT DISTINCT marks FROM score ORDER BY marks DESC LIMIT 1 OFFSET 1;

-- no LIMIT needed; yields NULL if there is no second value
SELECT MAX(marks) AS second FROM score WHERE marks < (SELECT MAX(marks) FROM score);

-- generalises to the Nth highest and keeps the names
SELECT student, marks FROM (
  SELECT student, marks, DENSE_RANK() OVER (ORDER BY marks DESC) AS r FROM score
) WHERE r = 2;

marks
-----
90

marks
-----
85

second
------
85

student  marks
-------  -----
Ben      85

What the interviewer is checking: Whether you ask about ties and empty results before writing the query.

Likely follow-up: Now return the second highest mark within each class. What changes?

They often express the same logic, so pick for clarity first and check performance second. A join is natural when you need columns from both sides. A subquery suits a quick existence or scalar check. A CTE (WITH name AS (...)) names an intermediate result, lets you build a query top to bottom, can be referenced more than once, and is the only way to write recursive queries such as walking an org chart.

On performance, modern optimizers usually flatten simple subqueries and CTEs into the same plan, but not always. For example, PostgreSQL before version 12 always materialised a CTE. Look at EXPLAIN rather than assuming one form is faster, and avoid correlated subqueries on large tables if a join does the same thing.

What the interviewer is checking: Judgement: readability first, plan evidence second, and knowing what a CTE uniquely offers.

Likely follow-up: When is a correlated subquery acceptable, and when does it turn into a per-row loop you should rewrite?

All three number rows within a window ordered by some column. ROW_NUMBER always gives unique numbers, breaking ties arbitrarily unless you add a tiebreaker. RANK gives tied rows the same number and then skips ahead (1, 1, 3). DENSE_RANK gives tied rows the same number without gaps (1, 1, 2).

Use ROW_NUMBER for "exactly one row per group" tasks like deduplication or picking the latest record. Use RANK or DENSE_RANK when ties should share a position, such as a leaderboard. PARTITION BY restarts the numbering for each group. For deterministic results, always make the ORDER BY unique or add a tiebreaker.

CREATE TABLE ride(id INTEGER PRIMARY KEY, city TEXT, driver TEXT, fare INTEGER);
INSERT INTO ride VALUES
 (1,'Pune','Ira',300),(2,'Pune','Ira',450),(3,'Pune','Jo',450),(4,'Pune','Kai',120),
 (5,'Goa','Lee',500),(6,'Goa','Mia',500),(7,'Goa','Lee',90);

SELECT id, city, fare,
  ROW_NUMBER() OVER w AS row_no,
  RANK()       OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM ride
WINDOW w AS (PARTITION BY city ORDER BY fare DESC)
ORDER BY city, row_no;

id  city  fare  row_no  rnk  dense
--  ----  ----  ------  ---  -----
5   Goa   500   1       1    1
6   Goa   500   2       1    1
7   Goa   90    3       3    2
2   Pune  450   1       1    1
3   Pune  450   2       1    1
1   Pune  300   3       3    2
4   Pune  120   4       4    3

What the interviewer is checking: Whether you know how ties are handled and can choose the right function for the job.

Likely follow-up: How would you return the top two fares per city, and which of the three functions should you use?

Use window functions, which compute a value for every row without collapsing rows the way GROUP BY does. A running total is SUM(units) OVER (ORDER BY day). The previous row's value comes from LAG(units) OVER (ORDER BY day), and LEAD looks forward. LAG returns NULL for the first row unless you give it a default.

Before window functions you would self-join the table or use a correlated subquery, which is longer and slower. Remember window functions run after WHERE and GROUP BY, so you cannot filter on them directly; wrap the query in a subquery or CTE and filter outside.

What the interviewer is checking: Comfort with window functions as the modern replacement for self-joins in analytics queries.

Likely follow-up: How would you compute a 7-day moving average, and what happens at the start of the series?

Normalization organises data so each fact is stored once. First normal form means atomic values with no repeating groups; second removes dependence on part of a composite key; third removes columns that depend on other non-key columns. The payoff is no update anomalies: changing a customer's address means editing one row, not hundreds.

Denormalization intentionally duplicates data to make reads cheaper, for example storing an order total or a product name on the order line, or keeping a precomputed counter. You trade write complexity and risk of inconsistency for fewer joins. Do it for measured read bottlenecks or reporting tables, and keep the normalized tables as the source of truth.

What the interviewer is checking: Whether you can name the anomalies normalization prevents and justify breaking the rules with a concrete trade-off.

Likely follow-up: If you store a denormalized copy of a price on each order line, how do you keep it correct?

Logically it is FROM and JOINs, then WHERE, GROUP BY, HAVING, window functions, SELECT (where aliases are created), DISTINCT, ORDER BY, and finally LIMIT. The optimizer may physically do things in a different order, but the result must match this logical order.

It explains common errors. You cannot use a column alias in WHERE in standard SQL and most engines, because WHERE runs before SELECT (SQLite is lenient here, so do not rely on that). You can use an alias in ORDER BY because that comes after. WHERE cannot filter on an aggregate or window function for the same reason.

What the interviewer is checking: A mental model of query evaluation that lets you predict errors and reason about filters.

Likely follow-up: Where does a window function's result become available, and how do you filter on it?

An index is a separate sorted structure that lets the database find rows without scanning the whole table. The common B-tree index keeps keys in sorted order in a shallow, wide tree, so a lookup touches only a few pages, and range scans and ORDER BY on the indexed column are cheap too.

The cost is that every INSERT, UPDATE and DELETE must also maintain each index, and indexes take disk space and memory. So index the columns you filter, join and sort on, not everything. Indexes also help little on tiny tables, or on low-selectivity columns like a status flag where most rows match.

What the interviewer is checking: That you see indexes as a read-versus-write trade-off and can name when they do not help.

Likely follow-up: Why is a column with only two distinct values usually a poor index choice by itself?

Yes. A composite B-tree index on (clinic_id, slot) is sorted by clinic_id first, then by slot within each clinic. So it can serve filters on clinic_id alone, or clinic_id and slot together, but not a filter on slot alone, because slot values are scattered across every clinic. This is the leftmost-prefix rule.

A good order puts equality columns first and a range column last, and favours the column your queries filter on most often. In the plan below the index is searched for the first two queries, while the slot-only query falls back to a full scan. Some engines can skip-scan, but do not count on it.

CREATE TABLE booking(id INTEGER PRIMARY KEY, clinic_id INTEGER, slot TEXT, patient TEXT);
-- (about 5,000 rows loaded here)
CREATE INDEX ix_clinic_slot ON booking(clinic_id, slot);

EXPLAIN QUERY PLAN SELECT * FROM booking WHERE clinic_id = 7 AND slot = '2025-01-05';
EXPLAIN QUERY PLAN SELECT * FROM booking WHERE clinic_id = 7;
EXPLAIN QUERY PLAN SELECT * FROM booking WHERE slot = '2025-01-05';

QUERY PLAN
`--SEARCH booking USING INDEX ix_clinic_slot (clinic_id=? AND slot=?)

QUERY PLAN
`--SEARCH booking USING INDEX ix_clinic_slot (clinic_id=?)

QUERY PLAN
`--SCAN booking

What the interviewer is checking: Whether you understand how an index is physically ordered and can design one for a real query pattern.

Likely follow-up: You have queries on (a), (a, b) and (b). Which indexes would you create?

Typical causes: wrapping the column in a function (WHERE UPPER(name) = 'X'), a leading wildcard (LIKE '%abc'), comparing with a mismatched type so the engine casts the column, or an OR across different columns. All of these stop the engine from using the sorted order of the plain column. Another cause is low selectivity: if most rows match, the optimizer rightly prefers a scan.

Fixes: rewrite the predicate so the column stands alone, create an expression index that matches the function, or fix the type. Also check for stale statistics. UPPER(patient) = 'P10' scans the whole index, while an index created on UPPER(patient) is searched directly.

What the interviewer is checking: Debugging skill: can you list the usual reasons an index is not used, and fix them?

Likely follow-up: How would you make a case-insensitive email lookup fast in the database you use?

A covering index contains every column a query needs, so the engine can answer it from the index alone without visiting the table rows. Skipping those extra lookups saves random I/O, which matters most when many rows match.

Say an index on (clinic_id, slot) finds the matching entries, but the query also wants patient, so each hit needs a trip to the table. Adding patient to the index makes the plan say COVERING INDEX. PostgreSQL and SQL Server let you add non-key columns with INCLUDE. The price is a larger index and slower writes, so cover only your hottest queries.

What the interviewer is checking: Depth on how indexes are read and a sense of cost versus benefit when adding wide indexes.

Likely follow-up: Why might adding a covering index make inserts measurably slower?

I look for a few things. Is a big table scanned when an index lookup was expected? In what order are tables joined, and with which method (nested loop, hash, merge)? Are the estimated row counts close to the actual ones? A large gap usually means stale statistics. Is there an expensive sort or temporary table, especially one spilling to disk?

Plain EXPLAIN shows the planner's estimate. Variants like PostgreSQL's EXPLAIN ANALYZE actually run the query and report real timings and row counts, so wrap writes in a transaction you roll back. Then I change one thing at a time, such as an index or a rewrite, and compare plans and timings. A plan with no scans can still be slow if it returns far too many rows.

What the interviewer is checking: A repeatable method for performance work rather than guessing at indexes.

Likely follow-up: The plan estimates 10 rows but the query returns 2 million. What do you try?

Take a money transfer between two accounts. Atomicity: both the debit and the credit happen, or neither does. Consistency: the transaction moves the database from one valid state to another, so constraints such as balance >= 0 still hold. Isolation: concurrent transactions do not see each other's half-finished work, to a degree set by the isolation level. Durability: once committed, the change survives a crash, typically through a write-ahead log.

In the demo, the credit succeeds but the debit violates a CHECK constraint, so the transaction is rolled back and both balances are unchanged. Note that "consistency" here partly depends on the constraints you define; the database cannot enforce rules it was never told about.

PRAGMA foreign_keys = ON;
CREATE TABLE account(id INTEGER PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0));
INSERT INTO account VALUES (1, 100), (2, 20);

BEGIN;
UPDATE account SET balance = balance + 150 WHERE id = 2;
UPDATE account SET balance = balance - 150 WHERE id = 1;   -- violates CHECK
ROLLBACK;

SELECT * FROM account;

Runtime error: CHECK constraint failed: balance >= 0 (19)

id  balance
--  -------
1   100
2   20

What the interviewer is checking: Whether you can tie each letter to a mechanism and an example rather than reciting definitions.

Likely follow-up: How does a write-ahead log give you both durability and atomicity?

The standard defines four levels, trading correctness for concurrency. Read uncommitted allows dirty reads (seeing uncommitted data). Read committed prevents those but allows non-repeatable reads (the same row changes between two reads). Repeatable read keeps rows stable but, per the standard, still permits phantoms (new rows matching a query appear). Serializable behaves as if transactions ran one after another.

Real engines differ. PostgreSQL, Oracle and SQL Server default to read committed, MySQL InnoDB defaults to repeatable read, and PostgreSQL treats read uncommitted as read committed. Higher levels cost more blocking or more retries on conflict. I pick the lowest level that is correct for the operation and handle lost updates explicitly.

What the interviewer is checking: Whether you know the anomalies by name, the defaults you actually work with, and that implementations deviate from the textbook.

Likely follow-up: Two requests read a balance, add 10 and write back. What can go wrong, and how do you prevent it?

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. Databases detect the cycle and abort one transaction (the victim) with an error, letting the other finish.

Prevention: acquire locks in a consistent order across all code paths, keep transactions short, do not wait for user input inside a transaction, and index the columns used in updates so fewer rows get locked. Handling: treat the deadlock error as retryable, re-run the whole transaction a limited number of times, and log the frequency. Transfers are a typical culprit: always lock the lower account ID first.

-- Session A                                -- Session B
BEGIN;                                      BEGIN;
UPDATE account SET balance = balance - 10   UPDATE account SET balance = balance - 10
  WHERE id = 1;                               WHERE id = 2;
UPDATE account SET balance = balance + 10   UPDATE account SET balance = balance + 10
  WHERE id = 2;   -- waits for B's lock       WHERE id = 1;   -- waits for A's lock
-- Cycle detected: the database aborts one session; the other proceeds.
-- Fix: both sessions update the lower id first, so they queue instead of cross.

What the interviewer is checking: Whether you can reason about lock ordering and design for retry rather than just define the term.

Likely follow-up: How would you find which queries are deadlocking in production?

I start from the access patterns and consistency needs, not from fashion. Relational databases fit structured data with relationships, ad hoc queries, multi-row transactions and strong integrity, which covers most business applications. NoSQL families fit specific shapes: document stores for flexible nested records, key-value stores for caching and sessions, wide-column stores for huge write-heavy data, graph stores for relationship traversal.

The trade-off is that many NoSQL systems give up joins, flexible queries or multi-record transactions in exchange for easier horizontal scaling and schema flexibility, though the gap has narrowed. If I am unsure, I default to a relational database and add a specialised store only when a measured need appears. Polyglot setups are common but add operational cost.

What the interviewer is checking: Judgement from requirements and trade-offs, not a slogan that one is better.

Likely follow-up: What would make you move one table of a relational system out into a key-value store?

With LIMIT 20 OFFSET 100000 the database must still walk and discard the first 100,000 rows, so each later page costs more. It can also skip or repeat rows if data is inserted between requests.

Keyset (seek) pagination remembers the last row seen and asks for rows after it: WHERE (posted, id) < (:last_posted, :last_id) ORDER BY posted DESC, id DESC LIMIT 20. With an index on those columns the engine seeks straight to the starting point, so every page costs about the same. Requirements: a deterministic, unique ordering (add the ID as a tiebreaker), and the client cannot jump to an arbitrary page number. Where row-value comparison is unsupported, expand it into an OR condition.

What the interviewer is checking: Awareness that query cost can depend on position in the result and knowledge of the standard fix.

Likely follow-up: How do you build the cursor token for keyset pagination when the sort is on a nullable column?

When a window has an ORDER BY but no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats all rows with the same ORDER BY value as peers of the current row, so tied rows are all included and share one result.

If you want a strict row-by-row running total, state the frame: ROWS UNBOUNDED PRECEDING. ROWS counts physical positions in the sorted order, so ties are split, and then you need a unique tiebreaker in the ORDER BY to make the result deterministic. This catches many analysts out when a running total jumps by two rows at once. Writing the frame out makes the intent obvious to the next reader.

CREATE TABLE ride(id INTEGER PRIMARY KEY, city TEXT, driver TEXT, fare INTEGER);
INSERT INTO ride VALUES (1,'Pune','Ira',300),(2,'Pune','Ira',450),(3,'Pune','Jo',450),(4,'Pune','Kai',120);

SELECT id, fare,
  SUM(fare) OVER (ORDER BY fare)                          AS default_frame,
  SUM(fare) OVER (ORDER BY fare ROWS UNBOUNDED PRECEDING) AS rows_frame
FROM ride ORDER BY fare, id;

id  fare  default_frame  rows_frame
--  ----  -------------  ----------
4   120   120            120
1   300   420            420
2   450   1320           870
3   450   1320           1320

What the interviewer is checking: Depth in window-frame semantics, a frequent source of subtle analytics bugs.

Likely follow-up: When would you prefer RANGE over ROWS on purpose?