LIMIT & OFFSET SQL practice problemMediumVerified answer

Large OFFSET — The Performance Cost

Return the last 2 employees by employee_id (skip the first 8).

  • CTEs
  • Sorting
  • Top-N

Interview brief

Understand the request

Data engineering lead The tail-end batch skips almost the entire table to retrieve late records only.

Get rows 12-15 (the last block) with a page-size-of-4 query: skip 11, return up to 4. Show employee_id, first_name, last_name.

Return

  • Return all employee columns.

Constraints

  • Use LIMIT 2 OFFSET 8 with ORDER BY employee_id.

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

On a 15-row table the cost is invisible. On a 10 million-row table, OFFSET 9_900_000 forces the engine to read and discard 9.9M rows just to return 100.

Hint 2

OFFSET cost grows linearly with the offset value. There is no index that fixes this — even with a perfect index the engine must walk it.

Hint 3

LIMIT 4 OFFSET 11

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 ORDER BY employee_id LIMIT 4 OFFSET 11;

Why this works

For deep pagination — like "page 1000 of search results" — switch to KEYSET (a.k.a. seek) pagination: instead of OFFSET, remember the last row's key and filter `WHERE id > :last_seen ORDER BY id LIMIT N`. The cost stays O(LIMIT) regardless of how deep the page is. That technique is question 22.

Expected result

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

employee_idfirst_namelast_name
12RachelGarcia
13KevinMartinez
14NicoleRodriguez
15BrianLee

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: