Build a Filtered Pagination Contract
Return page three with page size three for streams 'orders' and 'payments', newest events first.
- Filtering
- Sorting
- Top-N
Exercise brief
Understand the request
Reliability platform engineer An incident feed includes orders and payments events and exposes stable three-row pages.
Build a paginated view of IT and Sales employees only (department_id IN (1, 3)). Sort by hire_date descending with employee_id ascending as tie-breaker. With page size 2, return page 3. Show first_name, last_name, hire_date, department_id, employee_id.
Return
- Return event_id, event_time, stream, and severity.
- Return the seventh through ninth qualifying rows.
Constraints
- Filter before pagination.
- Order by event_time DESC and event_id DESC.
- Use the correct page-three offset.
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
The page is calculated over the filtered result, not the whole table.
Hint 2
Page three with three rows per page starts at OFFSET 6.
Hint 3
Use stream IN (...), then the complete newest-first order, then OFFSET and the row limit.
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 stream IN ('orders', 'payments') ORDER BY event_time DESC, event_id DESC LIMIT 3 OFFSET 6;Why this works
WHERE defines the eligible population before ORDER BY and pagination. A unique event_id fallback prevents equal timestamps from causing duplicates or omissions between pages.
Success check
The filtered population and deterministic page boundary both match the contract.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | event_time | stream | severity |
|---|---|---|---|
| 108 | 2025-05-03 08:00:00 | orders | 4 |
| 110 | 2025-05-02 14:00:00 | payments | 3 |
| 112 | 2025-05-01 09:00:00 | orders | 3 |
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.