Analyze document collaboration from CSV logs
Company: Notion
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are given two CSV files exported from a document collaboration product.
## Data
### 1) `doc_access.csv`
Each row represents a user accessing (viewing/editing) a document page.
| column | type | description |
|---|---|---|
| `page_id` | STRING/INT | Document/page identifier |
| `user_id` | STRING/INT | User who accessed the document |
| `event_ts` | TIMESTAMP | When the access happened (assume UTC) |
| `is_creator` | BOOLEAN | `TRUE` if this user is the creator/owner of the document; otherwise the user is a collaborator |
| `collab_source` | STRING | For collaborators only (`is_creator=FALSE`): how the user became a collaborator (e.g., invited, link share, org default, etc.) |
Notes/assumptions:
- A “collaborator” is any record with `is_creator = FALSE`.
- A user may access the same `page_id` multiple times; unless otherwise specified, treat collaboration at the **document level** (i.e., dedupe by `(page_id, user_id)` where appropriate).
### 2) `users.csv`
| column | type | description |
|---|---|---|
| `user_id` | STRING/INT | User identifier |
| `country` | STRING | User’s country |
## Tasks
### Q1) Distribution of collaborator sources
Compute the distribution of how collaborators got onto documents.
**Output:**
- `collab_source`
- `num_collaborators` (count of collaborator users; specify whether you count unique users overall or unique `(page_id, user_id)` pairs)
- `pct_of_collaborators`
### Q2) For a given user, who did they collaborate with most?
Given an input parameter `target_user_id`, find the other user they collaborated with the most.
Define “collaborated with” as: the two users both appear on the same `page_id` (after deduping to unique `(page_id, user_id)` memberships).
**Output:**
- `target_user_id`
- `top_collaborator_user_id`
- `num_shared_pages` (number of distinct `page_id` where both users appear)
Also describe how you would break ties (e.g., smallest user_id, most recent shared collaboration, etc.).
### Q3) Which country collaborates the most?
Determine which country’s users are most likely to collaborate with others.
Because “collaboration rate” can be defined multiple ways, do the following:
1. Propose a clear numerator and denominator for a per-country collaboration rate.
- Example definition (acceptable):
- Numerator: # of users in the country who have collaborated with at least one other distinct user on at least one page.
- Denominator: total # of users in the country.
2. Compute the collaboration rate by country and return the top country.
**Output:**
- `country`
- `collaboration_rate`
- any intermediate counts you used (e.g., `num_users_in_country`, `num_users_who_collaborated`).
Overview: This question evaluates data manipulation and analytical competencies—particularly deduplication, joins, aggregation, co-occurrence counting, and metric definition—applied to CSV access logs and user metadata.
Distribution of how collaborators were added to documents
You are given a CSV-style activity log that records users viewing/working on documents. Each row includes the document (page_id), the user (user_id), whether they are the creator of that document (is_creator), and if not the creator, how they became a collaborator (added_via).
Compute the distribution of collaborator acquisition sources: count how many distinct (page_id, user_id) collaborator relationships came from each added_via source. Treat NULL added_via as 'unknown'.
Return: added_via, collaborator_count.
Tables
document_activity(page_id INT, user_id INT, activity_ts TIMESTAMP, is_creator SMALLINT, added_via VARCHAR(30))
users(user_id INT, country VARCHAR(50))
Hints
- Count distinct (page_id, user_id) pairs so repeated activity events don't inflate the totals.
- Use COALESCE to bucket NULL sources into an 'unknown' category.
For a given user, find who they collaborated with the most
Using the same document activity log, define two users as having collaborated if they both appear on the same page_id (either as creator or collaborator). For user_id = 1, find the user(s) they collaborated with on the largest number of distinct pages.
Return all tied top collaborators.
Output columns: collaborator_user_id, shared_pages.
Tables
document_activity(page_id INT, user_id INT, activity_ts TIMESTAMP, is_creator SMALLINT, added_via VARCHAR(30))
users(user_id INT, country VARCHAR(50))
Hints
- First deduplicate to one row per (page_id, user_id) before counting shared pages.
- A self-join (or join via the target user's pages) plus DENSE_RANK helps handle ties.
Country with the highest collaboration rate
Using tables users(user_id, country) and document_activity, define a user as a "collaborator" if they have at least one activity row with is_creator = 0 (i.e., they collaborated on someone else's document at least once).
For each country, compute:
- total_users: number of users in that country
- collaborator_users: number of users in that country who are collaborators
- collaboration_rate = collaborator_users / total_users
Treat NULL country as 'Unknown'. Return the country (or countries) with the highest collaboration_rate (include ties).
Output columns: country, total_users, collaborator_users, collaboration_rate.
Tables
document_activity(page_id INT, user_id INT, activity_ts TIMESTAMP, is_creator SMALLINT, added_via VARCHAR(30))
users(user_id INT, country VARCHAR(50))
Hints
- Create a per-user flag: did this user ever appear with is_creator=0?
- Compute per-country totals and collaborator totals, then rank by the rate to keep ties.