Query US-based users and most active forums
Company: Reddit
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given user and post data for an online forum platform.
## Tables (schemas)
Assume the following tables:
### `users`
- `user_id` BIGINT (PK)
- `home_country_code` VARCHAR (nullable; e.g., 'US', 'CA')
- `created_at` TIMESTAMP
### `posts`
- `post_id` BIGINT (PK)
- `user_id` BIGINT (FK → `users.user_id`)
- `forum_id` BIGINT (FK → `forums.forum_id`)
- `created_at` TIMESTAMP
- `ip_country_code` VARCHAR (nullable; country inferred from the posting IP)
### `forums`
- `forum_id` BIGINT (PK)
- `forum_name` VARCHAR
## Time assumptions
- Use the last **30 days** of `posts.created_at` relative to `CURRENT_DATE`.
- Treat timestamps as UTC.
## Tasks
Write SQL queries for the following:
1) **Identify which users are “in the US.”**
- Define a user as “in the US” if **either**:
- `users.home_country_code = 'US'`, **or**
- among their posts in the last 30 days, **at least 50%** have `ip_country_code = 'US'`.
- Output columns:
- `user_id`
- `us_flag` (1 if in US else 0)
- `us_post_share_30d` (US posts / total posts in last 30 days; null if no posts)
2) **Find the most active forums in the last 30 days.**
- “Most active” should be measured by:
- primary metric: `post_count_30d`
- tie-breaker: `active_user_count_30d` (distinct posters)
- Output the **top 10** forums with columns:
- `forum_id`
- `forum_name`
- `post_count_30d`
- `active_user_count_30d`
Notes:
- Handle null country codes safely.
- You may use CTEs and window functions.
Quick Answer: This question evaluates SQL data manipulation skills, including time-based filtering, joins, aggregations, null-safe handling, ratio computation, distinct counts, and top-N ranking to determine user residency and forum activity.