Engineering practice · 30 MIN

Project · Paginate a changing activity feed

A composite cursor gives each page a stable boundary.

OFFSET scans and discards earlier rows and can shift when new records arrive. Keyset pagination selects records after the last returned sort tuple. For a descending (amount,id) order, select lower amounts or the same amount with a lower id. Production APIs should validate or sign opaque cursors and bind the same filters across pages. This exercise uses a fixed cursor to focus on the predicate.

Treat the function as a small service: define a contract, maintain an invariant, and test the boundaries.

Read the example

SELECT id, amount FROM sales WHERE amount IS NOT NULL ORDER BY amount DESC, id DESC LIMIT 2;

[[2,10],[1,10]]

Your challenge

Return the next two non-null sales after cursor (amount=10,id=1), as id,amount. Order by amount DESC,id DESC.

Solution cost: Potential O(log n + k) with a matching index and supported range plan; O(n log n) scan/sort otherwise. time · O(k) returned page, plus any engine sort workspace. space

Common trap

Filtering only amount < cursorAmount skips records tied with the cursor’s amount.

Further reading: SQLite query planner