Coding questions · Peregrine
Implement pagination and clump grouping
This question evaluates practical skills in paginated API integration, data aggregation and grouping (clump formation), ID-to-name resolution with caching, error and rate-limit handling, and designing unit tests and complexity analysis within the Data Manipulation (SQL/Python) domain.
You are given two pre-implemented APIs: (1) fetch_activities(page: int) -> { total_pages: int, activities: [ { activity_id: string, type: string, user_id: string, payload: object } ] }; (2) fetch_user_name(user_id: string) -> string. Perform the following:
- Pagination and print: Call fetch_activities for all pages, obtain the total count, aggregate every activity across pages, and print each activity.
- Group into clumps: Group activities by (type, user_name). Define a class Clump { type: string, name: string, activities: List[Activity] }. Return a list where each group is represented as a Clump, except when a group would contain exactly one activity—in that case return the single Activity object directly instead of a list/Clump.
- Replace IDs with names: When printing, show user_name (fetched via fetch_user_name) instead of user_id. Minimize redundant user lookups (e.g., with caching), and handle API errors, rate limits, and timeouts gracefully.
- Take‑home framing: Briefly describe how you would approach a take‑home variant where you must understand business context first, then aggregate the data, and finally filter results. State assumptions, edge cases, and validation steps.
- Complexity and tests: Provide time/space complexity and outline unit tests, including pagination boundaries, empty pages, missing users, and mixed single-item vs multi-item clumps.
Paginate Activities with User Names
Return the second page of activities with user names instead of user IDs.
Use activities and users. Sort activities by created_at, then activity_id. With page size 3, page 2 means rows 4 through 6 after sorting.
Return these columns:
activity_idtypeuser_namepayloadcreated_at, formatted asYYYY-MM-DD HH24:MI:SS
Use LIMIT 3 OFFSET 3.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| user_name | VARCHAR(100) | NOT NULL |
Sample data:
| user_id | user_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
| 4 | Dave |
Table activities
| column | type | constraints |
|---|---|---|
| activity_id | VARCHAR(20) | PRIMARY KEY, NOT NULL |
| type | VARCHAR(50) | NOT NULL |
| user_id | INT | NOT NULL |
| payload | TEXT | |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| activity_id | created_at | payload | type | user_id |
|---|---|---|---|---|
| A1 | 2025-05-28 09:00:00 | Login from web | login | 1 |
| A2 | 2025-05-29 08:30:00 | Login from mobile | login | 1 |
| A3 | 2025-05-29 10:15:00 | Order #1001 | purchase | 1 |
| A4 | 2025-05-30 09:45:00 | Login from web | login | 2 |
| A5 | 2025-05-30 11:00:00 | Order #1002 | purchase | 2 |
| A6 | 2025-05-31 14:20:00 | Order #1003 | purchase | 2 |
| A7 | 2025-05-31 16:05:00 | Left a review | comment | 3 |
Expected output
Second page of activities ordered by timestamp and id, with each activity joined to its user name.
| activity_id | created_at | payload | type | user_name |
|---|---|---|---|---|
| A4 | 2025-05-30 09:45:00 | Login from web | login | Bob |
| A5 | 2025-05-30 11:00:00 | Order #1002 | purchase | Bob |
| A6 | 2025-05-31 14:20:00 | Order #1003 | purchase | Bob |
Hints
- Join activities to users on user_id.
- Sort before applying LIMIT/OFFSET.
- Page 2 with page size 3 is LIMIT 3 OFFSET 3.
Group activities into clumps by type and user name
Using the same activities and users tables, group activities by (type, user_name). Define a "clump" as a group with more than one activity for the same (type, user_name). Groups with exactly one activity are considered singletons.
Write a query that returns one row per (type, user_name) group with the following columns:
- user_name
- type
- is_clump (TRUE if the group has more than one activity, FALSE otherwise)
- activity_ids: a comma-separated list of activity_id values in that group, ordered by activity_id
- activity_count: the number of activities in the group
Order the result by user_name, then type. Use the provided sample data to determine what the output should look like.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| user_name | VARCHAR(100) | NOT NULL |
Sample data:
| user_id | user_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
| 4 | Dave |
Table activities
| column | type | constraints |
|---|---|---|
| activity_id | VARCHAR(20) | PRIMARY KEY, NOT NULL |
| type | VARCHAR(50) | NOT NULL |
| user_id | INT | NOT NULL |
| payload | TEXT | |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| activity_id | created_at | payload | type | user_id |
|---|---|---|---|---|
| A1 | 2025-05-28 09:00:00 | Login from web | login | 1 |
| A2 | 2025-05-29 08:30:00 | Login from mobile | login | 1 |
| A3 | 2025-05-29 10:15:00 | Order #1001 | purchase | 1 |
| A4 | 2025-05-30 09:45:00 | Login from web | login | 2 |
| A5 | 2025-05-30 11:00:00 | Order #1002 | purchase | 2 |
| A6 | 2025-05-31 14:20:00 | Order #1003 | purchase | 2 |
| A7 | 2025-05-31 16:05:00 | Left a review | comment | 3 |
Expected output
One row per (type, user_name) group. Groups with more than one activity are marked as clumps. With the sample data, ('login','Alice') and ('purchase','Bob') are clumps; the others are singletons.
| activity_count | activity_ids | is_clump | type | user_name |
|---|---|---|---|---|
| 2 | A1, A2 | true | login | Alice |
| 1 | A3 | false | purchase | Alice |
| 1 | A4 | false | login | Bob |
| 2 | A5, A6 | true | purchase | Bob |
| 1 | A7 | false | comment | Carol |
Hints
- First join activities to users so you can group by user_name and type.
- Use COUNT(*) to detect clumps and a string aggregation function (such as STRING_AGG) to combine activity_ids.