Write SQL for reply-based recipient metrics
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: hard
Interview Round: Technical Screen
You work on a social product and are given two tables.
Assumptions (use these unless you state otherwise):
- All timestamps are in UTC.
- A “reply” is a row in `all_post` with `post_type = 'reply'` and a non-NULL `post_parent_id` pointing to the parent post.
- A user “receives a reply” when someone replies to one of the user’s posts; i.e., the recipient is the parent post’s `post_author_id`.
### Table schemas
`all_post`
- `post_id` BIGINT PRIMARY KEY
- `post_author_id` BIGINT NOT NULL -- FK to `user.user_id`
- `post_creation_time` TIMESTAMP NOT NULL
- `post_type` VARCHAR NOT NULL -- e.g., 'post', 'reply'
- `post_content` TEXT
- `post_parent_id` BIGINT NULL -- FK to `all_post.post_id` (parent post)
`user`
- `user_id` BIGINT PRIMARY KEY
- `age` INT
- `country` VARCHAR
### Tasks
1) **Users receiving 2 replies within 7 days:** Write a SQL query that returns the number of distinct users who have **at least one** of their posts receive **at least 2 replies** where the replies were created within **7 days after the parent post’s creation time**.
- Output: `user_cnt`
2) **% receiving replies from 2 distinct US repliers:** Among users who have received **at least 1 reply** to any of their posts, compute the percentage of those users who have received replies from **at least 2 distinct reply authors** whose `user.country = 'US'`.
- Output: `pct_users` (0–100 as a percentage, not a fraction)
Overview: This Data Manipulation (SQL/Python) question for a Data Scientist evaluates SQL skills including joins, aggregation, grouping, time-based filtering, deduplication, conditional counting, and percentage calculations across related tables (posts and users).
Write SQL for reply-based recipient metrics: (1) Count distinct users who have at least one of their posts receive at least 2 replies created within 7 days after the parent post’s creation time. Output: user_cnt. (2) Among users who have received at least 1 reply to any of their posts, compute the percentage who have received replies from at least 2 distinct reply authors whose user.country = 'US'. Output: pct_users (0–100).
Tables
all_post(post_id BIGINT, post_author_id BIGINT, post_creation_time TIMESTAMP, post_type VARCHAR, post_content TEXT, post_parent_id BIGINT)
user(user_id BIGINT, age INT, country VARCHAR)
Hints
- Join replies to their parent posts to get the recipient (parent post's author) and the parent_creation_time.
- For task 1, filter replies to those within [parent_time, parent_time + 7 days) and group by parent_post_id; then count distinct recipients with count >= 2.
Community answers
Answer by sagarai9980
**EASY TO UNDERSTAND AND SIMPLE
**
WITH POST AS (
SELECT post_id, post_author_id, post_creation_time
FROM all_post
WHERE post_type = 'post'
),
REPLY AS (
SELECT post_parent_id, post_creation_time
FROM all_post
WHERE post_type = 'reply'
),
POST_REPLY_COUNT AS (
SELECT
A.post_id,
A.post_author_id,
COUNT(*) AS reply_cnt
FROM POST A
LEFT JOIN REPLY B
ON A.post_id = B.post_parent_id
AND B.post_creation_time >= A.post_creation_time
AND B.post_creation_time <= A.post_creation_time + INTERVAL '7 days'
GROUP BY A.post_id, A.post_author_id
)
SELECT COUNT(DISTINCT post_author_id) AS user_cnt
FROM POST_REPLY_COUNT
WHERE reply_cnt >= 2;
Answer by sagarai9980
2nd Question Answer
WITH POST AS (
SELECT post_id, post_author_id
FROM all_post
WHERE post_type = 'post'
),
REPLY AS (
SELECT post_parent_id, post_author_id AS replier_id
FROM all_post
WHERE post_type = 'reply'
),
-- Map replies → post owners
USER_REPLIES AS (
SELECT
P.post_author_id AS user_id,
R.replier_id
FROM POST P
JOIN REPLY R
ON P.post_id = R.post_parent_id
),
-- Users who received at least 1 reply
USERS_WITH_REPLY AS (
SELECT DISTINCT user_id
FROM USER_REPLIES
),
-- Count distinct US repliers per user
US_REPLIER_COUNT AS (
SELECT
UR.user_id,
COUNT(DISTINCT UR.replier_id) AS us_replier_cnt
FROM USER_REPLIES UR
JOIN user U
ON UR.replier_id = U.user_id
WHERE U.country = 'US'
GROUP BY UR.user_id
)
SELECT
ROUND(
100.0 * COUNT(DISTINCT CASE WHEN U2.us_replier_cnt >= 2 THEN U1.user_id END)
/ COUNT(DISTINCT U1.user_id),
2
) AS pct_users
FROM USERS_WITH_REPLY U1
LEFT JOIN US_REPLIER_COUNT U2
ON U1.user_id = U2.user_id;