Coding questions · Notion

Analyze document collaboration from CSV logs

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.

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:

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.
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).

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.

Table `document_activity`

column	type	constraints
page_id	INT	NOT NULL
user_id	INT	NOT NULL
activity_ts	TIMESTAMP	NOT NULL
is_creator	SMALLINT	NOT NULL
added_via	VARCHAR(30)

Sample data:

activity_ts	added_via	is_creator	page_id	user_id
2025-05-01 09:00:00	NULL	1	100	1
2025-05-01 09:10:00	invite	0	100	2
2025-05-02 10:00:00	invite	0	100	2
2025-05-01 09:20:00	share_link	0	100	3
2025-05-03 08:00:00	invite	0	100	4
2025-05-05 12:00:00	NULL	1	101	2
2025-05-05 12:05:00	team_drive	0	101	1
2025-05-06 14:00:00	share_link	0	101	3
2025-05-07 09:00:00	NULL	1	102	3
2025-05-10 10:00:00	NULL	1	103	4
2025-05-10 10:30:00	invite	0	103	5
2025-05-11 11:00:00	share_link	0	103	6
2025-05-12 08:00:00	NULL	1	104	5
2025-05-12 08:15:00	invite	0	104	1
2025-05-12 08:20:00	invite	0	104	2
2025-05-12 09:00:00	NULL	0	104	6
2025-05-20 13:00:00	NULL	1	105	8
2025-05-25 09:00:00	NULL	1	106	1
2025-05-25 09:05:00	invite	0	106	3
2025-05-26 09:05:00	invite	0	106	3

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(50)

Sample data:

country	user_id
US	1
US	2
CN	3
IN	4
DE	5
NULL	6
US	7
JP	8

Expected output

One row per collaborator acquisition source (including 'unknown'), counting distinct (page_id, user_id) collaborator relationships.

added_via	collaborator_count
invite	6
share_link	3
team_drive	1
unknown	1

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.

Table `document_activity`

column	type	constraints
page_id	INT	NOT NULL
user_id	INT	NOT NULL
activity_ts	TIMESTAMP	NOT NULL
is_creator	SMALLINT	NOT NULL
added_via	VARCHAR(30)

Sample data:

activity_ts	added_via	is_creator	page_id	user_id
2025-05-01 09:00:00	NULL	1	100	1
2025-05-01 09:10:00	invite	0	100	2
2025-05-02 10:00:00	invite	0	100	2
2025-05-01 09:20:00	share_link	0	100	3
2025-05-03 08:00:00	invite	0	100	4
2025-05-05 12:00:00	NULL	1	101	2
2025-05-05 12:05:00	team_drive	0	101	1
2025-05-06 14:00:00	share_link	0	101	3
2025-05-07 09:00:00	NULL	1	102	3
2025-05-10 10:00:00	NULL	1	103	4
2025-05-10 10:30:00	invite	0	103	5
2025-05-11 11:00:00	share_link	0	103	6
2025-05-12 08:00:00	NULL	1	104	5
2025-05-12 08:15:00	invite	0	104	1
2025-05-12 08:20:00	invite	0	104	2
2025-05-12 09:00:00	NULL	0	104	6
2025-05-20 13:00:00	NULL	1	105	8
2025-05-25 09:00:00	NULL	1	106	1
2025-05-25 09:05:00	invite	0	106	3
2025-05-26 09:05:00	invite	0	106	3

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(50)

Sample data:

country	user_id
US	1
US	2
CN	3
IN	4
DE	5
NULL	6
US	7
JP	8

Expected output

User(s) who share the most distinct page_ids with user_id=1, including ties.

collaborator_user_id	shared_pages
2	3
3	3

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.

Table `document_activity`

column	type	constraints
page_id	INT	NOT NULL
user_id	INT	NOT NULL
activity_ts	TIMESTAMP	NOT NULL
is_creator	SMALLINT	NOT NULL
added_via	VARCHAR(30)

Sample data:

activity_ts	added_via	is_creator	page_id	user_id
2025-05-01 09:00:00	NULL	1	100	1
2025-05-01 09:10:00	invite	0	100	2
2025-05-02 10:00:00	invite	0	100	2
2025-05-01 09:20:00	share_link	0	100	3
2025-05-03 08:00:00	invite	0	100	4
2025-05-05 12:00:00	NULL	1	101	2
2025-05-05 12:05:00	team_drive	0	101	1
2025-05-06 14:00:00	share_link	0	101	3
2025-05-07 09:00:00	NULL	1	102	3
2025-05-10 10:00:00	NULL	1	103	4
2025-05-10 10:30:00	invite	0	103	5
2025-05-11 11:00:00	share_link	0	103	6
2025-05-12 08:00:00	NULL	1	104	5
2025-05-12 08:15:00	invite	0	104	1
2025-05-12 08:20:00	invite	0	104	2
2025-05-12 09:00:00	NULL	0	104	6
2025-05-20 13:00:00	NULL	1	105	8
2025-05-25 09:00:00	NULL	1	106	1
2025-05-25 09:05:00	invite	0	106	3
2025-05-26 09:05:00	invite	0	106	3

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(50)

Sample data:

country	user_id
US	1
US	2
CN	3
IN	4
DE	5
NULL	6
US	7
JP	8

Expected output

Country (or countries) with the maximum collaborator_users/total_users rate, including ties. NULL country is grouped as 'Unknown'.

collaboration_rate	collaborator_users	country	total_users
1	1	CN	1
1	1	DE	1
1	1	IN	1
1	1	Unknown	1

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.

Data

1) doc_access.csv

2) users.csv

Tasks

Q1) Distribution of collaborator sources

Q2) For a given user, who did they collaborate with most?

Q3) Which country collaborates the most?

Distribution of how collaborators were added to documents

Table document_activity

Table users

Expected output

Hints

For a given user, find who they collaborated with the most

Table document_activity

Table users

Expected output

Hints

Country with the highest collaboration rate

Table document_activity

Table users

Expected output

Hints

1) `doc_access.csv`

2) `users.csv`

Table `document_activity`

Table `users`

Table `document_activity`

Table `users`

Table `document_activity`

Table `users`