Posts and Replies Engagement
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
## Posts and Replies Engagement
A content platform stores user-generated **posts** and the **replies** that those posts receive. You need to answer two questions about reply engagement.
### Tables
**`posts`**
| column | type | description |
|-------------|-------------|----------------------------------------------|
| `post_id` | INTEGER | Primary key. Unique id of the post. |
| `author_id` | INTEGER | Id of the user who created the post. |
| `created_at`| TIMESTAMP | When the post was created (UTC). |
**`replies`**
| column | type | description |
|-------------|-------------|--------------------------------------------------------------|
| `reply_id` | INTEGER | Primary key. Unique id of the reply. |
| `post_id` | INTEGER | Foreign key referencing `posts.post_id`. The post replied to.|
| `author_id` | INTEGER | Id of the user who wrote the reply. |
| `created_at`| TIMESTAMP | When the reply was created (UTC). |
Notes / assumptions:
- A post may have zero, one, or many replies.
- `replies.post_id` always references an existing row in `posts`.
- A user may reply to their own post.
- All timestamps are in UTC.
### Part 1 — Reply count per post
Return every post together with the number of replies it has received. Posts with **zero** replies must still appear, with a reply count of `0`.
Output columns, in this exact order:
| column | type | description |
|---------------|---------|-----------------------------------|
| `post_id` | INTEGER | The post id. |
| `reply_count` | INTEGER | Number of replies for that post. |
Order the result by `reply_count` descending, breaking ties by `post_id` ascending.
### Part 2 — Posts with no replies
Return the `post_id` of every post that has received **no replies at all**.
Output column:
| column | type | description |
|-----------|---------|--------------------------------------|
| `post_id` | INTEGER | Id of a post that has zero replies. |
Order the result by `post_id` ascending.
Overview: This question tests practical SQL skills, specifically the use of LEFT JOIN and aggregate functions to compute engagement metrics across relational tables. It evaluates a data scientist's ability to handle zero-count rows and filter results using set-based logic, competencies commonly assessed in data manipulation interviews.
A content platform stores user-generated posts and the replies those posts receive. For every post, return the number of replies it has received. Posts with zero replies must still appear with a reply count of 0. Output columns in this exact order: post_id, reply_count. Order the result by reply_count descending, breaking ties by post_id ascending.
Tables
posts(post_id INTEGER, author_id INTEGER, created_at TIMESTAMP)
replies(reply_id INTEGER, post_id INTEGER, author_id INTEGER, created_at TIMESTAMP)
Hints
- Use a LEFT JOIN from posts to replies so that posts with no matching replies are still kept.
- COUNT(replies.reply_id) counts only non-NULL reply rows, so posts with zero replies get a count of 0 (COUNT(*) would wrongly return 1).