Coding questions · Meta
Posts and Replies Engagement
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 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_idalways references an existing row inposts.- 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.
Posts and Replies Engagement
Table posts
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | PRIMARY KEY |
| author_id | INTEGER | |
| created_at | TIMESTAMP |
Sample data:
| author_id | created_at | post_id |
|---|---|---|
| 100 | 2026-01-01 09:00:00 | 1 |
| 101 | 2026-01-01 10:00:00 | 2 |
| 100 | 2026-01-02 08:30:00 | 3 |
| 102 | 2026-01-02 12:00:00 | 4 |
| 103 | 2026-01-03 15:45:00 | 5 |
Table replies
| column | type | constraints |
|---|---|---|
| reply_id | INTEGER | PRIMARY KEY |
| post_id | INTEGER | |
| author_id | INTEGER | |
| created_at | TIMESTAMP |
Sample data:
| author_id | created_at | post_id | reply_id |
|---|---|---|---|
| 101 | 2026-01-01 09:15:00 | 1 | 1000 |
| 102 | 2026-01-01 09:20:00 | 1 | 1001 |
| 100 | 2026-01-01 09:25:00 | 1 | 1002 |
| 100 | 2026-01-01 10:05:00 | 2 | 1003 |
| 103 | 2026-01-01 10:10:00 | 2 | 1004 |
| 101 | 2026-01-02 12:30:00 | 4 | 1005 |
Expected output
| post_id | reply_count |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 4 | 1 |
| 3 | 0 |
| 5 | 0 |
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).
- GROUP BY posts.post_id, then ORDER BY reply_count DESC, post_id ASC.
- Part 2 (no replies): filter the LEFT JOIN with WHERE replies.reply_id IS NULL, or use NOT EXISTS / a subquery, then order by post_id.