Histogram of comments per user

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Aggregation. About 15 minutes. Part of the Pro drill bank.

A histogram shows how many users fall into each bucket. Here the bucket is the number of comments a user wrote in January 2026. social_users lists every user and comments lists each comment with its created_at date. For every possible comment_count from 0 up to the highest count any user reached, return user_count, the number of users who wrote exactly that many comments between 1 January and 31 January 2026, inclusive. A bucket that nobody reached must still appear, with user_count 0. Users who wrote no comments at all go in bucket 0. Return comment_count and user_count, ordered by comment_count.

Requirements

• Count each user's comments in January 2026 only.
• Include users with no comments at all, in bucket 0.
• Return every bucket from 0 up to the highest count, even when no user has that count.
• Return comment_count and user_count, ordered by comment_count.

Constraints

• Comments before 1 January or from 1 February onward do not count.
• Some users have no comments at all, so start from social_users and use a LEFT JOIN.
• The list of buckets must be built, for example from a generated range of numbers, so that empty buckets still get a row.

Examples

Input: social_users user_id 1 2 3 4 5 6 7 8 comments comment_id | user_id | created_at 1 | 1 | 2025-12-30 2 | 3 | 2026-01-05 3 | 4 | 2026-01-06 4 | 5 | 2026-01-07 5 | 5 | 2026-02-01 6 | 6 | 2026-01-08 7 | 6 | 2026-01-09 8 | 7 | 2026-01-10 9 | 7 | 2026-01-11 10 | 7 | 2026-01-12 11 | 7 | 2026-01-13 12 | 8 | 2026-01-14 ... Output: comment_count | user_count 0 | 2 1 | 3 2 | 2 3 | 0 4 | 1 Why this passes: Users 1 and 2 have no January comments (bucket 0). Users 3, 4 and 5 have one (user 5's February comment is ignored). Users 6 and 8 have two. User 7 has four. Nobody has three.

Input: social_users user_id 1 2 comments comment_id | user_id | created_at 1 | 1 | 2026-01-10 Output: comment_count | user_count 0 | 1 1 | 1 Why this passes: 2 users, one with a comment.

Topics: lakebench, sql, histogram, left join, zero buckets.

More SQL interview questions · All interview problems · Learn data engineering