Pagination patterns (cursor, offset) in HLD - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the correct pagination method for simple page numbers.

HLD

SELECT * FROM items ORDER BY id LIMIT 10 OFFSET [1];

Drag options to blanks, or click blank then click option'

Apage_size

Bcursor_value

Cpage_number * 10

Dtotal_count

Attempts:

3 left

💡 Hint

Common Mistakes

Using cursor_value instead of offset for offset pagination.

Offset pagination uses the page number multiplied by page size to skip records.

2fill in blank

medium

Complete the code to fetch the next page using cursor pagination.

HLD

SELECT * FROM items WHERE id > [1] ORDER BY id LIMIT 10;

Drag options to blanks, or click blank then click option'

Apage_number

Boffset_value

Ctotal_items

Dlast_seen_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using page_number or offset_value in cursor pagination query.

Cursor pagination uses the last seen item's id as the cursor to fetch the next set.

3fill in blank

hard

Fix the error in the pagination query to avoid skipping or duplicating items.

HLD

SELECT * FROM items WHERE id >= [1] ORDER BY id LIMIT 10;

Drag options to blanks, or click blank then click option'

Alast_seen_id

Bpage_number

Coffset_value

Dtotal_count

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>=' instead of '>' causing duplicate items on pages.

Using 'id >= last_seen_id' causes duplication; it should be 'id > last_seen_id' to avoid repeats.

4fill in blank

hard

Fill both blanks to implement offset pagination with page size and offset calculation.

HLD

SELECT * FROM items ORDER BY created_at DESC LIMIT [1] OFFSET [2];

Drag options to blanks, or click blank then click option'

Apage_size

Bpage_number

Cpage_number * page_size

Dcursor_value

Attempts:

3 left

💡 Hint

Common Mistakes

Using cursor_value in offset pagination query.

Limit is the page size, offset is page_number multiplied by page_size for offset pagination.

5fill in blank

hard

Fill all three blanks to implement cursor pagination with sorting and cursor parameter.

HLD

SELECT * FROM items WHERE [1] > [2] ORDER BY [3] ASC LIMIT 20;

Drag options to blanks, or click blank then click option'

Acreated_at

Bcursor_timestamp

Cid

Dpage_number

Attempts:

3 left

💡 Hint

Common Mistakes

Using page_number in cursor pagination query.

Cursor pagination uses a timestamp or unique field (created_at) to fetch items after the cursor timestamp, ordered by the same field.

Practice

1. What is the main advantage of cursor pagination over offset pagination in system design?

easy

A. Cursor pagination is faster and more reliable for large datasets.

B. Cursor pagination uses page numbers to navigate pages.

C. Cursor pagination requires less memory than offset pagination.

D. Cursor pagination always returns the total number of pages.

Solution

1. Step 1: Understand offset pagination limitations

   Offset pagination uses page numbers and offsets, which can be slow and inconsistent on large or changing datasets.

2. Step 2: Analyze cursor pagination benefits

   Cursor pagination uses a unique cursor (like an ID) to fetch the next set of results, making it faster and more reliable for large data.

3. Final Answer:

   Cursor pagination is faster and more reliable for large datasets. -> Option A

4. Quick Check:

   Cursor pagination = faster, reliable [OK]

Hint: Cursor uses unique IDs, offset uses page numbers [OK]

Common Mistakes:

• Confusing cursor with page number
• Thinking cursor pagination returns total pages
• Assuming offset is always faster

2. Which of the following is the correct way to represent an offset pagination request in an API call?

easy

A. /items?start=abc123&count=10

B. /items?cursor=abc123&limit=10

C. /items?page=2&limit=10

D. /items?offset=abc123&limit=10

Solution

1. Step 1: Identify offset pagination syntax

   Offset pagination typically uses page numbers and limit, e.g., page=2 and limit=10.

2. Step 2: Check options for correct syntax

   /items?page=2&limit=10 uses page=2 and limit=10, which matches offset pagination style.

3. Final Answer:

   /items?page=2&limit=10 -> Option C

4. Quick Check:

   Offset pagination = page + limit [OK]

Hint: Offset uses page and limit parameters [OK]

Common Mistakes:

• Using cursor parameter for offset pagination
• Confusing offset with cursor values
• Using invalid parameter names like start or offset with strings

3. Given the following cursor pagination code snippet, what will be the next cursor value returned?

items = [
  {"id": 101, "name": "A"},
  {"id": 102, "name": "B"},
  {"id": 103, "name": "C"}
]

last_cursor = 101
page_size = 2

next_page = [item for item in items if item["id"] > last_cursor][:page_size]
next_cursor = next_page[-1]["id"] if next_page else None
print(next_cursor)

medium

A. 102

B. 101

C. None

D. 103

Solution

1. Step 1: Filter items with id greater than last_cursor

   Items with id > 101 are 102 and 103.

2. Step 2: Take first 2 items as page size is 2

   Next page items are [{"id": 102, "name": "B"}, {"id": 103, "name": "C"}].

3. Step 3: Get last item's id as next cursor

   Last item id is 103, so next_cursor = 103.

4. Final Answer:

   103 -> Option D

5. Quick Check:

   Next cursor = last id in next page = 103 [OK]

Hint: Next cursor = last item's unique id in current page [OK]

Common Mistakes:

• Choosing last_cursor as next cursor
• Selecting first item id instead of last
• Ignoring page size limit

4. A developer implemented offset pagination but users report inconsistent results when data changes rapidly. What is the likely cause and fix?

medium

A. Cause: Using offset with changing data causes duplicates or skips; Fix: Switch to cursor pagination.

B. Cause: Using cursor pagination causes slow queries; Fix: Use offset pagination instead.

C. Cause: Not caching pages; Fix: Add caching layer for offset pages.

D. Cause: Using limit too high; Fix: Reduce limit to 1.

Solution

1. Step 1: Understand offset pagination issues with changing data

   Offset pagination can skip or repeat items if data changes between requests because offset is position-based.

2. Step 2: Identify solution with cursor pagination

   Cursor pagination uses unique IDs, avoiding duplicates or skips even if data changes.

3. Final Answer:

   Cause: Using offset with changing data causes duplicates or skips; Fix: Switch to cursor pagination. -> Option A

4. Quick Check:

   Offset pagination = inconsistent with changing data [OK]

Hint: Offset breaks with data changes; cursor stays consistent [OK]

Common Mistakes:

• Thinking caching fixes offset pagination issues
• Confusing cursor and offset pagination roles
• Reducing limit does not solve data inconsistency

5. You need to design a scalable API for a social media feed with millions of posts. Which pagination pattern should you choose and why?

hard

A. Offset pagination, because it is simple and supports jumping to any page.

B. Cursor pagination, because it handles large, changing datasets efficiently and avoids duplicates.

C. Offset pagination, because it requires less backend logic and is faster for large datasets.

D. Cursor pagination, because it returns total page count quickly.

Solution

1. Step 1: Analyze requirements for large, changing datasets

   Social media feeds have millions of posts and frequent updates, so pagination must be efficient and consistent.

2. Step 2: Compare offset and cursor pagination suitability

   Offset pagination is simple but slow and inconsistent with large, changing data. Cursor pagination efficiently handles these issues by using unique cursors.

3. Final Answer:

   Cursor pagination, because it handles large, changing datasets efficiently and avoids duplicates. -> Option B

4. Quick Check:

   Large dynamic data = cursor pagination best [OK]

Hint: For big changing data, use cursor pagination [OK]

Common Mistakes:

• Choosing offset for large dynamic datasets
• Assuming offset is faster for big data
• Thinking cursor returns total pages