Continue with a Composite Keyset Cursor
Return the next four events after cursor (event_time '2025-05-03 09:00:00', event_id 107) in descending keyset order.
- Filtering
- Sorting
- Top-N
Exercise brief
Understand the request
Streaming API engineer A high-volume newest-first feed must continue after a cursor without scanning earlier 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.
Return
- Return event_id, event_time, stream, and severity.
- Continue strictly after both cursor values.
Constraints
- Match the cursor predicate to the descending two-column order.
- Do not use OFFSET.
- Limit the page to four rows.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
page_events
event_idINTEGERevent_timeTIMESTAMPstreamTEXTseverityINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A non-unique timestamp needs the unique event_id in both the order and cursor.
Hint 2
For descending order, later pages use less-than comparisons.
Hint 3
Include older timestamps OR the same timestamp with a lower event_id, then limit four.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, event_time, stream, severity FROM page_events WHERE event_time < '2025-05-03 09:00:00' OR (event_time = '2025-05-03 09:00:00' AND event_id < 107) ORDER BY event_time DESC, event_id DESC LIMIT 4;Why this works
Composite keyset pagination seeks from the last ordered tuple. Repeating both sort keys in the predicate preserves timestamp peers and avoids OFFSET work.
Success check
The next page includes the remaining timestamp peer and does not repeat the cursor row.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | event_time | stream | severity |
|---|---|---|---|
| 106 | 2025-05-03 09:00:00 | orders | 4 |
| 108 | 2025-05-03 08:00:00 | orders | 4 |
| 110 | 2025-05-02 14:00:00 | payments | 3 |
| 109 | 2025-05-02 14:00:00 | inventory | 4 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
ORDER BY & Sorting
Practice deterministic SQL ordering with tie-breakers, custom priorities, NULL placement, expressions, joined data, aggregates, and portable top-N patterns.
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
Open the interactive workspace and practice across SQL topics.