Katkat
Back

Analyze Document Collaboration CSVs

NotionData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This problem tests your ability to clean, join, and analyze messy user-activity data using pandas or SQL, focusing on window functions and handling missing values.

Scenario

You are analyzing user collaboration data for a document editor. You have two datasets:

  1. edits.csv: Records of every document edit (user_id, doc_id, timestamp, edit_size_bytes).
  2. documents.csv: Metadata for documents (doc_id, owner_id, created_at, is_private).

Some user_id fields are null (indicating anonymous guests). You need to calculate the "Daily Active Contributor" (DAC) rate: the percentage of documents that had at least one edit from an owner vs. an anonymous guest on a given day.

Task

Write a function or query that returns a table with the following columns: date, percent_owner_edits, and percent_guest_edits.

Constraints

  • timestamp is in ISO format.
  • Ignore edits to documents where is_private is true.
  • Handle potential duplicate timestamps if multiple edits occurred in the same second.

Example

Input edits:

user_iddoc_idtimestampedit_size_bytes
101A2023-01-0150
NULLA2023-01-0120

Input documents:

doc_idowner_idis_private
A101False

Output:

datepercent_owner_editspercent_guest_edits
2023-01-01100.0100.0

Note: If a doc has both owner and guest edits, both metrics count it for that day.

Hints

Hint 1

First, filter out private documents and cast timestamps to a date-only format.

Hint 2

Perform a left join between your cleaned edits table and the documents table to associate every edit with an owner_id.

Hint 3

Use a group-by on date and document, then compare the edit's user_id against the document's owner_id to create boolean flags for 'is_owner_edit' and 'is_guest_edit'.

Follow-up questions

  • How would you handle documents that have zero edits on a specific day to avoid 'gaps' in your time-series report?
  • If the edit volume grows to billions of rows, how would you optimize this calculation for a production environment?
  • How would you determine if an anonymous user is actually a logged-out returning user based on IP or cookie identifiers?