Compute reply-based engagement metrics
You are working on a microblogging application, similar to Twitter. Posts and replies are stored in the following tables:
Tables
all_post
post_idBIGINT PKpost_author_idBIGINT FK → user.user_id (Author of this post/reply)post_creation_timeTIMESTAMP (Assume UTC)post_typeVARCHAR (Values:'post'for an original post,'reply'for a reply)post_contentTEXTpost_parent_idBIGINT NULL FK → all_post.post_id- For
post_type='reply',post_parent_idpoints to the post being replied to. - For
post_type='post',post_parent_idis NULL.
- For
user
user_idBIGINT PKageINTcountryVARCHAR (e.g.,'US','CA', ...)
Task A
For each original post (post_type='post'), consider replies to that post where the reply was created within 7 days (inclusive) after the original post’s post_creation_time.
Return the number of distinct users (post_author_id of the original post) who have at least one original post that received ≥ 2 replies within that 7-day window.
Output:
num_users
Task B
A user “receives a reply” if any of their original posts received a reply.
Compute the percentage of users who received replies from at least two distinct repliers where both repliers are US users (user.country='US'). Repliers are the authors of the reply posts.
Clarification: Use as denominator the number of distinct users who received at least one reply (i.e., exclude users who never received a reply).
Output:
pct_users(as a percentage from 0 to 100)
AI interview prep
Core dilemma
Accurately and efficiently correlating parent posts with their qualifying replies (based on time and type) and then performing multi-level aggregations (counting replies per post, then posts per user, then distinct users) to derive specific engagement metrics, especially when dealing with potentially large datasets where naive approaches could be very slow.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Compute reply-based engagement metrics
You are working on a microblogging application, similar to Twitter. Posts and replies are stored in the following tables:
Tables
all_post
post_idBIGINT PKpost_author_idBIGINT FK → user.user_id (Author of this post/reply)post_creation_timeTIMESTAMP (Assume UTC)post_typeVARCHAR (Values:'post'for an original post,'reply'for a reply)post_contentTEXTpost_parent_idBIGINT NULL FK → all_post.post_id- For
post_type='reply',post_parent_idpoints to the post being replied to. - For
post_type='post',post_parent_idis NULL.
- For
user
user_idBIGINT PKageINTcountryVARCHAR (e.g.,'US','CA', ...)
Task A
For each original post (post_type='post'), consider replies to that post where the reply was created within 7 days (inclusive) after the original post’s post_creation_time.
Return the number of distinct users (post_author_id of the original post) who have at least one original post that received ≥ 2 replies within that 7-day window.
Output:
num_users
Task B
A user “receives a reply” if any of their original posts received a reply.
Compute the percentage of users who received replies from at least two distinct repliers where both repliers are US users (user.country='US'). Repliers are the authors of the reply posts.
Clarification: Use as denominator the number of distinct users who received at least one reply (i.e., exclude users who never received a reply).
Output:
pct_users(as a percentage from 0 to 100)