Compute reply-based engagement metrics
Company: Tools For Humanity
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You work on a microblogging app (Twitter-like). Posts and replies are stored in the tables below.
### Tables
#### `all_post`
- `post_id` BIGINT **PK**
- `post_author_id` BIGINT **FK → user.user_id** (author of this post/reply)
- `post_creation_time` TIMESTAMP (assume UTC)
- `post_type` VARCHAR (values: `'post'` for an original post, `'reply'` for a reply)
- `post_content` TEXT
- `post_parent_id` BIGINT NULL **FK → all_post.post_id**
- For `post_type='reply'`, `post_parent_id` points to the post being replied to.
- For `post_type='post'`, `post_parent_id` is NULL.
#### `user`
- `user_id` BIGINT **PK**
- `age` INT
- `country` VARCHAR (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.
**Output:**
- `pct_users` (as a percentage from 0 to 100)
**Clarification:** Use as denominator the number of distinct users who received **at least one** reply (i.e., exclude users who never received a reply).
Overview: This question evaluates proficiency in data manipulation and analytics, emphasizing SQL/Python skills for temporal filtering, joins between posts and users, deduplication, aggregation (distinct counts), and percentage calculations used to compute reply-based engagement metrics.
Count users who received at least two replies in the last 7 days
You are given a table `all_post` for an app like Twitter. A row can be either an original post (`post_type = 'post'`) or a reply (`post_type = 'reply'`). Replies reference the parent post via `post_parent_id`.
Treat "current date" as 2025-06-01. Write a SQL query to return how many distinct users received at least 2 replies to any of their posts during the date range FROM 2025-05-26 TO 2025-06-01 (inclusive). A user "receives a reply" if someone creates a `reply` whose `post_parent_id` points to a post authored by that user.
Tables
users(user_id INT, age INT, country VARCHAR(50))
all_post(post_id INT, post_author_id INT, post_creation_time TIMESTAMP, post_type VARCHAR(10), post_content VARCHAR(500), post_parent_id INT)
Hints
- A reply is a row with post_type='reply' and post_parent_id pointing to a parent post.
- Join replies to their parent post to identify who received the reply, then aggregate by recipient.
Percentage of users who received replies from at least two distinct US users
You are given tables `all_post` and `users` for an app like Twitter.
Compute the percentage of all users who have received replies from at least TWO DISTINCT users whose `country = 'US'`. A user "receives a reply" if someone creates a `reply` whose `post_parent_id` points to a post authored by that user.
Return a single row with one column `pct_users` as a percentage from 0 to 100 rounded to 2 decimal places.
Tables
users(user_id INT, age INT, country VARCHAR(50))
all_post(post_id INT, post_author_id INT, post_creation_time TIMESTAMP, post_type VARCHAR(10), post_content VARCHAR(500), post_parent_id INT)
Hints
- First find, per recipient, how many distinct US repliers have replied to their posts.
- Then divide the number of qualifying recipients by the total number of users.