Compute reply-based engagement metrics

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).

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.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY
age	INT	NOT NULL
country	VARCHAR(50)	NOT NULL

Sample data:

age	country	user_id
25	US	1
31	US	2
28	CA	3
22	US	4
35	UK	5
27	US	6

Table all_post

column	type	constraints
post_id	INT	PRIMARY KEY
post_author_id	INT	NOT NULL
post_creation_time	TIMESTAMP	NOT NULL
post_type	VARCHAR(10)	NOT NULL
post_content	VARCHAR(500)
post_parent_id	INT

Sample data:

post_author_id	post_content	post_creation_time	post_id	post_parent_id	post_type
3	Hello world	2025-05-20 09:00:00	100	NULL	post
5	My first post	2025-05-27 10:00:00	101	NULL	post
3	Another update	2025-05-30 12:00:00	102	NULL	post
2	Ask me anything	2025-05-31 08:30:00	103	NULL	post
1	Nice post!	2025-05-28 14:00:00	200	101	reply
2	Agree	2025-05-29 16:00:00	201	101	reply
4	Interesting	2025-05-30 18:00:00	202	101	reply
6	Thanks for sharing	2025-05-31 13:00:00	203	102	reply
1	Good point	2025-06-01 09:15:00	204	102	reply
5	Replying here	2025-05-31 20:00:00	205	103	reply
2	Old reply	2025-05-25 11:00:00	206	100	reply
4	Starting the week	2025-05-26 07:00:00	207	100	reply
1	Another reply	2025-06-01 21:00:00	208	103	reply

Expected output

Return a single row with the number of distinct users who received at least 2 replies between 2025-05-26 and 2025-06-01 (inclusive).

user_count
3

Hints

1. A reply is a row with post_type='reply' and post_parent_id pointing to a parent post.
2. 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.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY
age	INT	NOT NULL
country	VARCHAR(50)	NOT NULL

Sample data:

age	country	user_id
25	US	1
31	US	2
28	CA	3
22	US	4
35	UK	5
27	US	6

Table all_post

column	type	constraints
post_id	INT	PRIMARY KEY
post_author_id	INT	NOT NULL
post_creation_time	TIMESTAMP	NOT NULL
post_type	VARCHAR(10)	NOT NULL
post_content	VARCHAR(500)
post_parent_id	INT

Sample data:

post_author_id	post_content	post_creation_time	post_id	post_parent_id	post_type
3	Hello world	2025-05-20 09:00:00	100	NULL	post
5	My first post	2025-05-27 10:00:00	101	NULL	post
3	Another update	2025-05-30 12:00:00	102	NULL	post
2	Ask me anything	2025-05-31 08:30:00	103	NULL	post
1	Nice post!	2025-05-28 14:00:00	200	101	reply
2	Agree	2025-05-29 16:00:00	201	101	reply
4	Interesting	2025-05-30 18:00:00	202	101	reply
6	Thanks for sharing	2025-05-31 13:00:00	203	102	reply
1	Good point	2025-06-01 09:15:00	204	102	reply
5	Replying here	2025-05-31 20:00:00	205	103	reply
2	Old reply	2025-05-25 11:00:00	206	100	reply
4	Starting the week	2025-05-26 07:00:00	207	100	reply
1	Another reply	2025-06-01 21:00:00	208	103	reply

Expected output

Return a single row with pct_users, the percent of all users who have received replies from at least 2 distinct US users, rounded to 2 decimals.

pct_users
33.33

Hints

1. First find, per recipient, how many distinct US repliers have replied to their posts.
2. Then divide the number of qualifying recipients by the total number of users.