Compute reply-based user metrics in 7 days

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.