Compute reply-based user metrics in 7 days
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are analyzing discussions on a social platform.
## Tables
### `all_post`
- `post_id` (BIGINT, PK)
- `post_author_id` (BIGINT, FK → `user.user_id`)
- `post_creation_time` (TIMESTAMP, UTC)
- `post_type` (VARCHAR) — e.g., `'root'` for an original post and `'reply'` for a reply
- `post_content` (TEXT)
- `post_parent_id` (BIGINT, nullable) — if this is a reply, points to the parent post’s `post_id`; `NULL` for root posts
### `user`
- `user_id` (BIGINT, PK)
- `age` (INT)
- `country` (VARCHAR) — e.g., `'US'`
## Definitions / Assumptions
- A **reply** is a row in `all_post` with `post_parent_id IS NOT NULL`.
- A user **receives a reply** when someone creates a reply whose `post_parent_id` points to a post authored by that user.
- “Within 7 days” means `reply.post_creation_time` is in `[parent.post_creation_time, parent.post_creation_time + INTERVAL '7 days']` (inclusive). Use UTC.
## Tasks
1) **Count users who received at least two replies within 7 days**
- Count distinct `post_author_id` (the parent post’s author) who have **at least one parent post** that received **≥ 2 replies** within 7 days of that parent post’s creation.
- Output: a single row with `num_users`.
2) **Percentage of users who received replies from ≥ 2 distinct US users**
- For each recipient user (the parent post’s author), look at all replies to their posts (no time window unless you choose to state one).
- A recipient qualifies if they received replies from **at least 2 distinct repliers** where the replier’s `user.country = 'US'`.
- Denominator: all users who received **at least 1 reply** (from anyone).
- Output: a single row with `pct_users` (as a decimal or percent; specify which in your query).
Note: Task (1) and task (2) have different qualifying criteria; be careful not to mix the populations.
Overview: This question evaluates competency in SQL-based data manipulation and temporal user-metric calculation, focusing on aggregations and distinct-user counts derived from reply relationships within defined time windows.
Count users who received at least two replies in a 7-day window
You are given a forum posts table `all_post` and a `users` table.
A post is considered a **reply** when `post_type = 'reply'` and `post_parent_id` is not NULL. A user **receives a reply** when another user's reply has `post_parent_id` pointing to a post authored by them (the immediate parent post).
Compute **how many distinct users received at least 2 replies** between **2025-05-26 and 2025-06-01 (inclusive)**, based on the reply's `post_creation_time`.
Return a single row with the count as `users_with_2plus_replies`.
Tables
users(user_id BIGINT, age INT, country VARCHAR(2))
all_post(post_id BIGINT, post_author_id BIGINT, post_creation_time TIMESTAMP, post_type VARCHAR(20), post_content VARCHAR(500), post_parent_id BIGINT)
Hints
- Self-join `all_post` from reply to its parent post to find who received the reply.
- Filter by the reply's timestamp, then group by the recipient user and apply HAVING COUNT(*) >= 2.
Percentage of replied-to users who received replies from at least two distinct US users
Using the same tables and definitions as in Question 1:
A user **receives a reply** when another user's reply has `post_parent_id` pointing to a post authored by them (the immediate parent post).
Between **2025-05-26 and 2025-06-01 (inclusive)**, compute the **percentage of users who received at least one reply** that **received replies from at least 2 distinct repliers located in the US** (`users.country = 'US'`).
Return a single row with:
- `qualified_users` = number of users who received replies from at least 2 distinct US repliers
- `replied_users` = number of users who received at least one reply (from any country)
- `pct_qualified` = `qualified_users / replied_users * 100`, rounded to 2 decimals
If there are no replied-to users in the window, return 0 for `pct_qualified`.
Tables
users(user_id BIGINT, age INT, country VARCHAR(2))
all_post(post_id BIGINT, post_author_id BIGINT, post_creation_time TIMESTAMP, post_type VARCHAR(20), post_content VARCHAR(500), post_parent_id BIGINT)
Hints
- Compute the set of recipients who received any reply in the window (denominator).
- For the numerator, restrict repliers to country = 'US' and count DISTINCT replier IDs per recipient.
Community answers
Answer by genghiswang
WITH root_post. as
(SELECT POST_ID, POSTING_author_ID, post_creation_time
FROM
WHERE type = 'root'),
reply_post as (
SELECT post_partent_id, post_creation_time as reply_time, post_id as reply_id
FROM
where type = 'reply'),
SELECT count(distinct post_authro_id) as num_users
FROM root_post
INNER JOIN reply_post
ON post_id = post_parent_id
WHERE date_diff(post_create_time, reply_time) <= 7
HAVING count(reply_id) >= 2