Coding questions · Meta
Compute reply-based user metrics in 7 days
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.
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 replypost_content(TEXT)post_parent_id(BIGINT, nullable) — if this is a reply, points to the parent post’spost_id;NULLfor root posts
user
user_id(BIGINT, PK)age(INT)country(VARCHAR) — e.g.,'US'
Definitions / Assumptions
- A reply is a row in
all_postwithpost_parent_id IS NOT NULL. - A user receives a reply when someone creates a reply whose
post_parent_idpoints to a post authored by that user. - “Within 7 days” means
reply.post_creation_timeis in[parent.post_creation_time, parent.post_creation_time + INTERVAL '7 days'](inclusive). Use UTC.
Tasks
-
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.
- Count distinct
-
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.
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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | BIGINT | PRIMARY KEY, NOT NULL |
| age | INT | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
Sample data:
| age | country | user_id |
|---|---|---|
| 29 | US | 1 |
| 35 | US | 2 |
| 26 | CA | 3 |
| 41 | IN | 4 |
| 33 | US | 5 |
| 24 | GB | 6 |
Table all_post
| column | type | constraints |
|---|---|---|
| post_id | BIGINT | PRIMARY KEY, NOT NULL |
| post_author_id | BIGINT | NOT NULL |
| post_creation_time | TIMESTAMP | NOT NULL |
| post_type | VARCHAR(20) | NOT NULL |
| post_content | VARCHAR(500) | |
| post_parent_id | BIGINT |
Sample data:
| post_author_id | post_content | post_creation_time | post_id | post_parent_id | post_type |
|---|---|---|---|---|---|
| 1 | Original post by user 1 | 2025-05-20 10:00:00 | 100 | NULL | post |
| 2 | Original post by user 2 | 2025-05-27 09:00:00 | 101 | NULL | post |
| 3 | Original post by user 3 | 2025-05-28 12:00:00 | 102 | NULL | post |
| 4 | Original post by user 4 | 2025-05-30 08:30:00 | 103 | NULL | post |
| 2 | Reply to post 100 by user 2 | 2025-05-26 11:00:00 | 200 | 100 | reply |
| 3 | Reply to post 100 by user 3 | 2025-05-27 15:00:00 | 201 | 100 | reply |
| 5 | Reply to post 100 by user 5 | 2025-05-31 13:00:00 | 202 | 100 | reply |
| 1 | Reply to post 101 by user 1 | 2025-05-28 14:00:00 | 203 | 101 | reply |
| 3 | Reply to post 101 by user 3 | 2025-05-29 16:00:00 | 204 | 101 | reply |
| 2 | Reply to post 102 by user 2 | 2025-06-01 09:30:00 | 205 | 102 | reply |
| 6 | Reply to post 103 by user 6 (outside window) | 2025-05-25 10:00:00 | 206 | 103 | reply |
| 5 | Reply to post 103 by user 5 | 2025-05-26 18:00:00 | 207 | 103 | reply |
| 2 | Reply to post 103 by user 2 | 2025-05-27 19:00:00 | 208 | 103 | reply |
Expected output
A single row containing the number of distinct users who received at least two replies in the window 2025-05-26 to 2025-06-01 (inclusive).
| users_with_2plus_replies |
|---|
| 3 |
Hints
- Self-join
all_postfrom 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 repliersreplied_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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | BIGINT | PRIMARY KEY, NOT NULL |
| age | INT | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
Sample data:
| age | country | user_id |
|---|---|---|
| 29 | US | 1 |
| 35 | US | 2 |
| 26 | CA | 3 |
| 41 | IN | 4 |
| 33 | US | 5 |
| 24 | GB | 6 |
Table all_post
| column | type | constraints |
|---|---|---|
| post_id | BIGINT | PRIMARY KEY, NOT NULL |
| post_author_id | BIGINT | NOT NULL |
| post_creation_time | TIMESTAMP | NOT NULL |
| post_type | VARCHAR(20) | NOT NULL |
| post_content | VARCHAR(500) | |
| post_parent_id | BIGINT |
Sample data:
| post_author_id | post_content | post_creation_time | post_id | post_parent_id | post_type |
|---|---|---|---|---|---|
| 1 | Original post by user 1 | 2025-05-20 10:00:00 | 100 | NULL | post |
| 2 | Original post by user 2 | 2025-05-27 09:00:00 | 101 | NULL | post |
| 3 | Original post by user 3 | 2025-05-28 12:00:00 | 102 | NULL | post |
| 4 | Original post by user 4 | 2025-05-30 08:30:00 | 103 | NULL | post |
| 2 | Reply to post 100 by user 2 | 2025-05-26 11:00:00 | 200 | 100 | reply |
| 3 | Reply to post 100 by user 3 | 2025-05-27 15:00:00 | 201 | 100 | reply |
| 5 | Reply to post 100 by user 5 | 2025-05-31 13:00:00 | 202 | 100 | reply |
| 1 | Reply to post 101 by user 1 | 2025-05-28 14:00:00 | 203 | 101 | reply |
| 3 | Reply to post 101 by user 3 | 2025-05-29 16:00:00 | 204 | 101 | reply |
| 2 | Reply to post 102 by user 2 | 2025-06-01 09:30:00 | 205 | 102 | reply |
| 6 | Reply to post 103 by user 6 (outside window) | 2025-05-25 10:00:00 | 206 | 103 | reply |
| 5 | Reply to post 103 by user 5 | 2025-05-26 18:00:00 | 207 | 103 | reply |
| 2 | Reply to post 103 by user 2 | 2025-05-27 19:00:00 | 208 | 103 | reply |
Expected output
A single row showing how many users received replies from at least two distinct US repliers, how many users received any reply, and the resulting percentage (rounded to 2 decimals).
| pct_qualified | qualified_users | replied_users |
|---|---|---|
| 50 | 2 | 4 |
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.