LIMIT & OFFSET SQL practice problemHardVerified answer

Keyset Pagination — The Fix for Slow Deep Pages

Instead of OFFSET, fetch the next page using the LAST seen employee_id from the previous page. Imagine the previous page ended at employee_id = 8. Get the next 4 employees with employee_id > 8, ordered by employee_id ascending. Show employee_id, first_name, last_name.

CTEs
Filtering
Sorting
Top-N

Interview brief

Understand the request

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

employee_idINTEGER
first_nameTEXT
last_nameTEXT

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

OFFSET pagination scans (OFFSET + LIMIT) rows. Keyset pagination uses a WHERE filter on a unique, ordered column and scans only LIMIT rows — no matter how deep the page is.

Hint 2

The cursor is the value of the sort key on the LAST row of the previous page. Pass it through the URL: `?after=8`.

Hint 3

WHERE employee_id > 8 ORDER BY employee_id LIMIT 4

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation

SELECT employee_id, first_name, last_name FROM employees WHERE employee_id > 8 ORDER BY employee_id LIMIT 4;

Why this works

Trade-offs vs OFFSET: keyset pagination is dramatically faster at depth (constant time vs linear) but it cannot jump to an arbitrary page number — it only does 'next' and 'prev'. For multi-column sorts the cursor becomes a tuple: `WHERE (sort_a, sort_b, id) > (:a, :b, :id)`. Most modern infinite-scroll APIs use keyset pagination.

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee_id	first_name	last_name
9	Chris	Anderson
10	Emma	Thomas
11	Alex	Miller
12	Rachel	Garcia

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: