Analyze Document Collaboration CSVs

You are provided with two CSV files: document_activity.csv and users.csv.

document_activity.csv fields:

• page_id STRING — document identifier
• user_id STRING — user who viewed or worked on the document
• viewed_at TIMESTAMP — event time in UTC
• is_creator BOOLEAN — true if the user created the document
• collaborator_source STRING NULL — for non-creators only, the channel by which the collaborator reached the document (e.g., invite, shared link, search). This field is NULL for creators.

users.csv fields:

• user_id STRING — user identifier
• country STRING — user's country

Assumptions:

• document_activity.user_id can be joined to users.user_id.
• Each row represents a single user-page event.
• A document is considered collaborative if at least two distinct users interact with the same page_id.
• No date filtering is required; use the full dataset.
• All timestamps are in UTC.

Tasks:

1. Distribution of collaborator acquisition channels:

   • Consider only rows where is_creator = false.
   • Compute the distribution of collaborator_source.
   • Return: collaborator_source, collaborator_count, collaborator_share.
   • Define collaborator_share as collaborator_count / total_non_creator_rows.

2. Find who collaborated most with a given user:

   • Given an input user_id, find the other user or users who collaborated most often with that user.
   • Define collaboration as appearing on the same page_id.
   • Count distinct shared page_id values, not raw events.
   • Return: input_user_id, other_user_id, shared_page_count.
   • If multiple users tie for the maximum, return all tied users ordered by other_user_id.

3. Identify which country has the highest collaboration rate:

   • Join to users.csv to get each user's country.
   • For each country, define:
     • total_active_users = number of distinct users from that country who appear in document_activity.
     • collaborating_users = number of those users who appear on at least one collaborative document.
     • collaboration_rate = collaborating_users / total_active_users.
   • Return: country, collaborating_users, total_active_users, collaboration_rate.
   • Rank countries by collaboration_rate descending.

Constraints & Assumptions:

• Preserve the scope, facts, inputs, and requested outputs from the prompt.
• If a detail is unspecified, state a reasonable assumption before relying on it.
• Keep the answer concise but concrete for implementation or evaluation.