Coding questions · Meta
Write SQL for comment analytics
This question evaluates proficiency in SQL analytics, specifically joins, filtering, aggregation (distinct counts), percentile functions, time-windowed grouping, handling soft-deleted records, and writing efficient queries via CTEs.
You are given the following schema and tiny sample data.
Schema:
- users(user_id INT PRIMARY KEY, country VARCHAR, created_at DATE)
- posts(post_id INT PRIMARY KEY, author_id INT, created_at DATE)
- comments(comment_id INT PRIMARY KEY, post_id INT, user_id INT, created_at DATE, content VARCHAR, is_deleted BOOLEAN)
Sample tables (minimal, not exhaustive):
users
user_id | country | created_at
1 | US | 2025-08-01
2 | US | 2025-08-05
3 | CA | 2025-08-10
4 | US | 2025-08-20
posts
post_id | author_id | created_at
10 | 1 | 2025-08-25
11 | 2 | 2025-08-28
12 | 3 | 2025-08-30
comments
comment_id | post_id | user_id | created_at | content | is_deleted
100 | 10 | 2 | 2025-08-26 | "Nice!" | false
101 | 10 | 3 | 2025-08-27 | "Great post" | false
102 | 11 | 1 | 2025-08-28 | "Thanks" | false
103 | 11 | 2 | 2025-08-30 | "I agree" | true
104 | 12 | 4 | 2025-08-31 | "Wow" | false
105 | 12 | 1 | 2025-09-01 | "Following" | false
106 | 12 | 3 | 2025-09-01 | "Awesome insights"| false
107 | 10 | 1 | 2025-09-01 | "Self" | false
Assume "today" = 2025-09-01. For the 7-day window 2025-08-26 through 2025-09-01 (inclusive), write ANSI SQL (you may assume percentile_cont is available) to: (a) Return the top 3 posts by distinct commenter count, excluding deleted comments. Output: post_id, unique_commenters, distinct_commenter_countries. Break ties by newer posts (posts.created_at desc) then lower post_id. (b) For each post and day in the window, compute P50 and P90 of comment text length (use length(content)) over non-deleted comments. Output: day, post_id, p50_len, p90_len. Include days with at least 1 non-deleted comment. (c) List users who commented on their own posts in the window, with columns: user_id, self_comment_count, last_self_comment_at. Order by self_comment_count desc, user_id asc. Aim for readable, efficient queries using CTEs; avoid unnecessary scans.
Top 3 posts by distinct commenter count in a 7-day window
Using the tables below, and considering only the 7-day window from 2025-08-26 through 2025-09-01 (inclusive), return the top 3 posts by distinct commenter count, excluding deleted comments. For each qualifying post, output: post_id, unique_commenters (COUNT of distinct users who commented on the post in the window), and distinct_commenter_countries (COUNT of distinct countries of those commenters). Break ties by newer posts first (posts.created_at DESC) and then lower post_id. Use readable ANSI SQL; you may assume percentile_cont is available, though it is not needed for this sub-question.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| country | created_at | user_id |
|---|---|---|
| US | 2025-08-01 | 1 |
| US | 2025-08-05 | 2 |
| CA | 2025-08-10 | 3 |
| US | 2025-08-20 | 4 |
Table posts
| column | type | constraints |
|---|---|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| author_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| author_id | created_at | post_id |
|---|---|---|
| 1 | 2025-08-25 | 10 |
| 2 | 2025-08-28 | 11 |
| 3 | 2025-08-30 | 12 |
Table comments
| column | type | constraints |
|---|---|---|
| comment_id | INT | PRIMARY KEY, NOT NULL |
| post_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
| content | VARCHAR(255) | NOT NULL |
| is_deleted | BOOLEAN | NOT NULL |
Sample data:
| comment_id | content | created_at | is_deleted | post_id | user_id |
|---|---|---|---|---|---|
| 100 | Nice! | 2025-08-26 | false | 10 | 2 |
| 101 | Great post | 2025-08-27 | false | 10 | 3 |
| 102 | Thanks | 2025-08-28 | false | 11 | 1 |
| 103 | I agree | 2025-08-30 | true | 11 | 2 |
| 104 | Wow | 2025-08-31 | false | 12 | 4 |
| 105 | Following | 2025-09-01 | false | 12 | 1 |
| 106 | Awesome insights | 2025-09-01 | false | 12 | 3 |
| 107 | Self | 2025-09-01 | false | 10 | 1 |
Expected output
Top 3 posts within 2025-08-26 to 2025-09-01 by number of distinct non-deleted commenters, with tie-breaking by newer post_created_at then lower post_id. For each such post, also return the count of distinct commenter countries.
| distinct_commenter_countries | post_id | unique_commenters |
|---|---|---|
| 2 | 12 | 3 |
| 2 | 10 | 3 |
| 1 | 11 | 1 |
Hints
- First aggregate comments per post (COUNT DISTINCT user_id and DISTINCT country) over the 7-day window, excluding deleted comments.
- Use a window function like ROW_NUMBER() to rank posts and then filter to the top 3 with the specified tie-breakers.
Daily P50 and P90 comment length per post in a 7-day window
Using the same tables, and considering only non-deleted comments between 2025-08-26 and 2025-09-01 (inclusive), compute for each post and each day in that window the P50 and P90 of comment text length. Use LENGTH(content) for the comment length and percentile_cont for the percentiles. Output columns: day (the comment date), post_id, p50_len, p90_len. Include only (day, post_id) combinations that have at least one non-deleted comment. Aim for readable ANSI SQL.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| country | created_at | user_id |
|---|---|---|
| US | 2025-08-01 | 1 |
| US | 2025-08-05 | 2 |
| CA | 2025-08-10 | 3 |
| US | 2025-08-20 | 4 |
Table posts
| column | type | constraints |
|---|---|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| author_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| author_id | created_at | post_id |
|---|---|---|
| 1 | 2025-08-25 | 10 |
| 2 | 2025-08-28 | 11 |
| 3 | 2025-08-30 | 12 |
Table comments
| column | type | constraints |
|---|---|---|
| comment_id | INT | PRIMARY KEY, NOT NULL |
| post_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
| content | VARCHAR(255) | NOT NULL |
| is_deleted | BOOLEAN | NOT NULL |
Sample data:
| comment_id | content | created_at | is_deleted | post_id | user_id |
|---|---|---|---|---|---|
| 100 | Nice! | 2025-08-26 | false | 10 | 2 |
| 101 | Great post | 2025-08-27 | false | 10 | 3 |
| 102 | Thanks | 2025-08-28 | false | 11 | 1 |
| 103 | I agree | 2025-08-30 | true | 11 | 2 |
| 104 | Wow | 2025-08-31 | false | 12 | 4 |
| 105 | Following | 2025-09-01 | false | 12 | 1 |
| 106 | Awesome insights | 2025-09-01 | false | 12 | 3 |
| 107 | Self | 2025-09-01 | false | 10 | 1 |
Expected output
For each (day, post_id) with at least one non-deleted comment between 2025-08-26 and 2025-09-01, the median (P50) and 90th percentile (P90) of LENGTH(content) computed via percentile_cont.
| day | p50_len | p90_len | post_id |
|---|---|---|---|
| 2025-08-26 | 5 | 5 | 10 |
| 2025-08-27 | 10 | 10 | 10 |
| 2025-08-28 | 6 | 6 | 11 |
| 2025-08-31 | 3 | 3 | 12 |
| 2025-09-01 | 4 | 4 | 10 |
| 2025-09-01 | 12.5 | 15.3 | 12 |
Hints
- Filter to the 7-day window and non-deleted comments, then group by created_at (as day) and post_id.
- Use percentile_cont(0.5) and percentile_cont(0.9) WITHIN GROUP (ORDER BY LENGTH(content)) to compute P50 and P90.
Users who self-commented in a 7-day window
Using the same tables, find users who commented on their own posts between 2025-08-26 and 2025-09-01 (inclusive), considering only non-deleted comments. A self-comment is a comment where comments.user_id = posts.author_id for the commented post. For each such user, output: user_id, self_comment_count (number of their non-deleted self-comments in the window), and last_self_comment_at (the latest comment date in the window). Order the result by self_comment_count DESC, then user_id ASC.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| country | created_at | user_id |
|---|---|---|
| US | 2025-08-01 | 1 |
| US | 2025-08-05 | 2 |
| CA | 2025-08-10 | 3 |
| US | 2025-08-20 | 4 |
Table posts
| column | type | constraints |
|---|---|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| author_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| author_id | created_at | post_id |
|---|---|---|
| 1 | 2025-08-25 | 10 |
| 2 | 2025-08-28 | 11 |
| 3 | 2025-08-30 | 12 |
Table comments
| column | type | constraints |
|---|---|---|
| comment_id | INT | PRIMARY KEY, NOT NULL |
| post_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
| content | VARCHAR(255) | NOT NULL |
| is_deleted | BOOLEAN | NOT NULL |
Sample data:
| comment_id | content | created_at | is_deleted | post_id | user_id |
|---|---|---|---|---|---|
| 100 | Nice! | 2025-08-26 | false | 10 | 2 |
| 101 | Great post | 2025-08-27 | false | 10 | 3 |
| 102 | Thanks | 2025-08-28 | false | 11 | 1 |
| 103 | I agree | 2025-08-30 | true | 11 | 2 |
| 104 | Wow | 2025-08-31 | false | 12 | 4 |
| 105 | Following | 2025-09-01 | false | 12 | 1 |
| 106 | Awesome insights | 2025-09-01 | false | 12 | 3 |
| 107 | Self | 2025-09-01 | false | 10 | 1 |
Expected output
All users who made at least one non-deleted comment on their own posts in the window, with the count of such comments and the most recent self-comment date, ordered by count descending then user_id ascending.
| last_self_comment_at | self_comment_count | user_id |
|---|---|---|
| 2025-09-01 | 1 | 1 |
| 2025-09-01 | 1 | 3 |
Hints
- Join comments to posts and filter to rows where comments.user_id equals posts.author_id, within the date window and not deleted.
- Aggregate per user_id using COUNT(*) and MAX(created_at), then order by the requested columns.