Quick Overview

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.

Implement pagination and clump grouping

Company: Peregrine

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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.

Overview: 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.

Read the full Peregrine Software Engineer interview experience this question came from

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_id` - `type` - `user_name` - `payload` - `created_at`, formatted as `YYYY-MM-DD HH24:MI:SS` Use `LIMIT 3 OFFSET 3`.

Tables

users(user_id INT, user_name VARCHAR(100))

activities(activity_id VARCHAR(20), type VARCHAR(50), user_id INT, payload TEXT, created_at TIMESTAMP)

Hints

  1. Join activities to users on user_id.
  2. Sort before applying LIMIT/OFFSET.

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.

Tables

users(user_id INT, user_name VARCHAR(100))

activities(activity_id VARCHAR(20), type VARCHAR(50), user_id INT, payload TEXT, created_at TIMESTAMP)

Hints

  1. First join activities to users so you can group by user_name and type.
  2. Use COUNT(*) to detect clumps and a string aggregation function (such as STRING_AGG) to combine activity_ids.

Loading coding console...