Coding questions · Coinbase
Implement filters and cursor pagination
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.
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.
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.
Table transactions
| column | type | constraints |
|---|---|---|
| transaction_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| start_date | DATE | NOT NULL |
| end_date | DATE | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | end_date | start_date | transaction_id | user_id |
|---|---|---|---|---|
| 75 | 2025-01-06 | 2025-01-05 | 1 | 101 |
| 150 | 2025-01-21 | 2025-01-20 | 2 | 101 |
| 80 | 2025-02-02 | 2025-02-01 | 3 | 101 |
| 100 | 2025-01-11 | 2025-01-10 | 4 | 102 |
| 60 | 2024-12-31 | 2024-12-30 | 5 | 101 |
| 30 | 2025-01-16 | 2025-01-15 | 6 | 101 |
| 90 | 2025-01-26 | 2025-01-25 | 7 | 103 |
| 220 | 2025-01-29 | 2025-01-28 | 8 | 101 |
Expected output
All transactions for user_id = 101 with start_date between '2025-01-01' and '2025-01-31' inclusive and amount between 50 and 200 inclusive, ordered by start_date then transaction_id.
| amount | end_date | start_date | transaction_id | user_id |
|---|---|---|---|---|
| 75 | 2025-01-06 | 2025-01-05 | 1 | 101 |
| 150 | 2025-01-21 | 2025-01-20 | 2 | 101 |
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.
Table transactions
| column | type | constraints |
|---|---|---|
| transaction_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| start_date | DATE | NOT NULL |
| end_date | DATE | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | end_date | start_date | transaction_id | user_id |
|---|---|---|---|---|
| 75 | 2025-01-06 | 2025-01-05 | 1 | 101 |
| 150 | 2025-01-21 | 2025-01-20 | 2 | 101 |
| 80 | 2025-02-02 | 2025-02-01 | 3 | 101 |
| 100 | 2025-01-11 | 2025-01-10 | 4 | 102 |
| 60 | 2024-12-31 | 2024-12-30 | 5 | 101 |
| 30 | 2025-01-16 | 2025-01-15 | 6 | 101 |
| 90 | 2025-01-26 | 2025-01-25 | 7 | 103 |
| 220 | 2025-01-29 | 2025-01-28 | 8 | 101 |
Expected output
The second page of 3 transactions sorting strictly after the cursor (start_date = '2025-01-10', transaction_id = 4), ordered by start_date then transaction_id.
| amount | end_date | start_date | transaction_id | user_id |
|---|---|---|---|---|
| 30.00 | 2025-01-16 | 2025-01-15 | 6 | 101 |
| 150.00 | 2025-01-21 | 2025-01-20 | 2 | 101 |
| 90.00 | 2025-01-26 | 2025-01-25 | 7 | 103 |
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.
- Use LIMIT for the page size, and keep the ORDER BY identical to the comparison keys so the page is deterministic.