Workspace event analytics in Python and SQL: top events, plans and blocks

A data phone screen over a document workspace's product data: an event log, a user table and the tree of blocks that documents are made of. The questions come one after another on the same tables. Write each in Python (or pandas) and be ready to write it again in SQL; settle what is counted (events or distinct users) and how ties break before you start.

Part 1: the event type with the most events

Given a list of product events, count the events of each type and return the type with the highest count. Write it in Python, then in SQL over an events(user_id, event_type, timestamp) table.

Unless you agree on a different interface with the interviewer, assume:

• top_event_types(events: list[tuple[str, str, int]]) -> list[str] — each event is (user_id, event_type, timestamp). Return every event type tied for the highest count, sorted alphabetically. An empty input returns [].

events (type only): view, edit, view, share, edit
top_event_types(events)  ->  ["edit", "view"]    # both have 2

events (type only): view, view, edit
top_event_types(events)  ->  ["view"]

A strong answer covers:

• A single pass with a counter: O(n) time and O(t) memory for t types, then one pass to collect the maximum.
• The SQL with GROUP BY event_type, and returning ties with RANK() OVER (ORDER BY COUNT(*) DESC) or a HAVING COUNT(*) = (SELECT MAX(...)) subquery rather than LIMIT 1.
• Edge cases: empty input, every type tied, null event types.

Part 2: the most popular plan among users of that event type

You have product event logs and a user table. First find the event type that occurs most often. Then, among the users who produced that event type, find the most common plan type. Write it in Python (or pandas), and be ready to write the same thing in SQL.

Unless you agree on a different interface with the interviewer, assume:

• events: rows of (user_id, event_type, timestamp).
• users: rows of (user_id, plan_type), for example free, plus, business.
• top_plan_for_top_event(events, users) -> tuple[str, str] returns (event_type, plan_type).
• The top event type is counted by number of events. The top plan is counted by number of distinct users with that plan who produced the event at least once. Break ties alphabetically.

events (user, type):  u1 page_view x3, u2 page_view, u3 page_view, u1 edit, u4 edit
users:                u1 free, u2 plus, u3 plus, u4 business

page_view has 5 events, edit has 2          -> top event type: page_view
users with page_view: u1 free, u2 plus, u3 plus
top_plan_for_top_event(events, users)  ->  ("page_view", "plus")

Counting events instead of users would give free (3 events against 2), which is why the rule must be settled first.

A strong answer covers:

• Two passes: count events per type, then collect the distinct users of the top type and count their plans through a join (hash map from user to plan).
• The SQL version with GROUP BY, COUNT(DISTINCT user_id) and ORDER BY ... LIMIT 1, or RANK() to keep ties visible.
• Handling users with no plan row (inner versus left join) and how that changes the answer.
• Checking the result by hand on a tiny table before running on the full data.

Part 3: the share of blocks never edited

A document is made of blocks, and every change to a block is logged as an event. From the event log, compute what percentage of blocks were never edited after they were created. Write it in Python, and be ready to write the same thing in SQL.

Unless you agree on a different interface with the interviewer, assume:

• events: rows of (block_id, event_type, timestamp), where event_type is "create", "edit" or "delete". Rows are not sorted.
• never_edited_pct(events) -> float — among blocks that have a create event, the percentage with no edit event later than their creation, rounded to 2 decimals. Returns 0.0 when no block was created.
• A block deleted without being edited counts as never edited. An edit for a block with no create event is ignored.

("b1", "create", 1)
("b2", "create", 2)
("b5", "edit",   2)    # no create for b5: ignored
("b3", "create", 3)
("b4", "create", 4)
("b1", "edit",   5)
("b4", "delete", 6)
("b3", "edit",   8)

created: b1, b2, b3, b4    edited after creation: b1, b3
never_edited_pct(events)  ->  50.0

A strong answer covers:

• One pass to record each block's creation time, a second to find blocks with a later edit; O(n) time and O(b) memory for b blocks.
• The SQL: a LEFT JOIN (or NOT EXISTS) from creates to later edits, and 100.0 * ... / COUNT(*) so integer division doesn't round to 0.
• Stating each choice about the denominator and odd rows, and showing how the answer moves if it changes.
• Testing on a hand-built table like the one above.

Part 4: the block type with the most children

A workspace stores documents as a tree of blocks. Each block has a type (page, heading, text, list, ...) and a parent, except top-level blocks. Find the block type whose blocks have the most children in total.

Unless you agree on a different interface with the interviewer, assume:

• blocks: rows of (block_id, block_type, parent_id), where parent_id is None for a top-level block.
• type_with_most_children(blocks) -> str — for each type, add up the number of direct children of every block of that type, and return the type with the largest total. Break ties alphabetically.

p1 page   (top level)        p2 page   (top level)
h1 heading -> p1             l1 list   -> p1          l2 list -> p1
t1 text    -> p2
i1, i2, i3 list_item -> l1   i4, i5 list_item -> l2

children by parent type: page 3 (p1) + 1 (p2) = 4, list 3 (l1) + 2 (l2) = 5
type_with_most_children(blocks)  ->  "list"

p1 and l1 both have 3 children, so the answer depends on totalling per type, not taking the single biggest block.

A strong answer covers:

• A first pass to map each block id to its type, a second to count each row against its parent's type: O(n) time.
• Why two passes are needed when children can come before parents, or how to do it in one by storing pending counts per parent id.
• The SQL: a self-join of blocks to their parent, GROUP BY parent.block_type, ordered by count and type.
• Orphan rows and top-level blocks handled on purpose.

Follow-ups

• Return the top event type for each day, or for each country.
• Return the share of each plan among those users instead of only the top one.
• Report the percentage per block type, or per week of creation.
• Return the average number of children per block of each type, counting childless blocks as 0.