Implement filters and cursor pagination
Company: Coinbase
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Design and implement a transaction query module over a dataset or database where each transaction has startDate, endDate, userId, and amount. Requirements:
(
1) Provide per-field filters exposed via setter methods (e.g., setDateRange(start, end), setUserId(id), setAmountRange(min, max)); filters combine conjunctively.
(
2) Implement cursor-based pagination: given pageSize and an optional opaque cursor, return exactly pageSize matching transactions and a next cursor. Assume you can call a DB. Specify the stable sort keys used for pagination, define and encode the cursor, handle empty pages and end-of-results, address inserts/updates between requests, and provide code-level API signatures plus complexity analysis.
Overview: This question evaluates a candidate's ability to design and implement cursor-based pagination, conjunctive per-field filtering, stable sort key selection, API surface design, and complexity analysis for a database-backed transaction query module.
Filter Transactions by Date, User, and Amount
You are given a transactions table where each transaction has a start_date, end_date, user_id, and amount. Write a SQL query that returns all transactions for user_id = 101 where:
- start_date is between '2025-01-01' and '2025-01-31' (inclusive), and
- amount is between 50 and 200 (inclusive).
All filters must be applied conjunctively (a row must satisfy all conditions). Order the result by start_date ascending, then transaction_id ascending.
Tables
transactions(transaction_id INT, user_id INT, start_date DATE, end_date DATE, amount DECIMAL(10,2))
Hints
- Use a WHERE clause with multiple conditions combined using AND.
- BETWEEN can be used for both dates and numeric ranges.
Implement Cursor-Based Pagination over Transactions
## Cursor-Based (Keyset) Pagination over Transactions
You are given a `transactions` table with columns `transaction_id`, `user_id`, `start_date`, `end_date`, and `amount`.
Implement **cursor-based (keyset) pagination** over the transactions, using the stable sort order **`start_date` ascending, then `transaction_id` ascending** as the tiebreaker. The cursor is the `(start_date, transaction_id)` pair of the **last row of the previous page**: a page returns only rows that sort strictly after that pair, limited to `page_size` rows.
**For this question, return the second page using these concrete cursor values:**
- `page_size` = **3**
- cursor `start_date` = **'2025-01-10'**
- cursor `transaction_id` = **4**
In other words, return the **next 3 transactions** that come strictly after the row `(start_date = '2025-01-10', transaction_id = 4)` in the order `start_date ASC, transaction_id ASC`.
**Output:** one row per returned transaction, with columns `transaction_id`, `user_id`, `start_date`, `end_date`, `amount`, ordered by `start_date` ascending and then `transaction_id` ascending.
Tables
transactions(transaction_id INT, user_id INT, start_date DATE, end_date DATE, amount DECIMAL(10,2))
Hints
- Sort by the full key (start_date, then transaction_id) and take only the rows that sort strictly after the cursor pair.
- PostgreSQL lets you compare a tuple of columns directly: (start_date, transaction_id) > (cursor_date, cursor_id) does the lexicographic 'strictly after' check for you.