You are provided with two CSV files containing document activity and user information, and your task is to analyze collaboration patterns.

Data Files

document_activity.csv

Each row represents a document view event.

• page_id (STRING): Unique document identifier.
• user_id (STRING): Identifier of the user who viewed the document.
• viewed_at (TIMESTAMP): The UTC timestamp of the view event.
• is_creator (BOOLEAN): true if the user is the document creator, false otherwise.
• collaborator_source (STRING NULL): For non-creators, this indicates how the user accessed the document (e.g., invite link, email invite, search, direct). This field may be NULL for creators.

users.csv

• user_id (STRING): Unique user identifier.
• country (STRING): The country of the user.

Assumptions

• The join key between document_activity and users tables is user_id.
• All available data should be used; no date filtering is required.
• For collaboration-related questions, unless explicitly stated otherwise, deduplicate to distinct (page_id, user_id) pairs to count unique user-document interactions, not individual view events.
• A document can have one creator and multiple collaborators.

Tasks

1. Compute the distribution of collaborator acquisition sources.

   • Focus only on non-creators (is_creator = false).
   • The metric should be based on distinct collaborator-document pairs (page_id, user_id).
   • Required output columns:
     • collaborator_source
     • collaborator_count
     • collaborator_share

2. Given an input user_id, find which other users collaborated with that user the most.

   • Two users are considered collaborators if they have both appeared on the same page_id at least once.
   • Count distinct shared documents, not view events.
   • Exclude the target user themselves from the results.
   • Required output columns:
     • target_user_id
     • co_user_id
     • shared_page_count
   • Return the user(s) with the maximum shared_page_count.

3. Find which country's users are most likely to collaborate with others.

   • A user is defined as having "collaborated with others" if they appear on at least one document with at least one other distinct user (i.e., the document has 2 or more distinct users total).
   • For each country, compute the following metrics:
     • collaborating_users: The number of distinct users in that country who collaborated with at least one other user.
     • total_users: The total number of distinct users in that country, sourced from users.csv.
     • collaboration_rate: Calculated as collaborating_users / total_users.
   • Required output columns:
     • country
     • collaborating_users
     • total_users
     • collaboration_rate
   • Rank countries from highest to lowest collaboration rate.