SQL: Solving Histogram of Tweets

A short SQL walkthrough for Histogram of Tweets.

The problem is from DataLemur — Histogram of Tweets.

Problem

Using the tweets table, count how many users posted one tweet, two tweets, and so on during 2022. Return tweet_bucket (tweet count) and users_num (user count).

Table structure

Column name	Type
tweet_id	integer
user_id	integer
msg	string
tweet_date	timestamp

Example input

Times are omitted for readability:

tweet_id	user_id	msg	tweet_date
214252	111	Am considering taking Tesla private at $420. Funding secured.	2021-12-30
739252	111	Despite the constant negative press covfefe	2022-01-01
846402	111	Following @NickSinghTech on Twitter changed my life!	2022-02-14
241425	254	If the salary is so competitive why won’t you tell me what it is?	2022-03-01
231574	148	I no longer have a manager. I can't be managed	2022-03-23

Example output

tweet_bucket	users_num
1	2
2	1

Users 254 and 148 each posted one tweet in 2022, while user 111 posted two. The tweet from December 2021 is excluded.

Approach

My first thought was: before I can count users with the same number of tweets, I need to know how many tweets each user posted. So I filter the data to 2022, then use GROUP BY user_id with COUNT(tweet_id). This becomes the inner query: one row per user, with their tweet count stored as bucket.

Now I can treat that result as a new table. If two users each posted one tweet, they each have a row with bucket = 1. In the outer query, I group by bucket and use COUNT(user_id) to count those users. The two queries answer two questions in order: how many tweets did each user post, then how many users share that count?

Query

SELECT
    bucket AS tweet_bucket,
    COUNT(user_id) AS users_num
FROM (
    SELECT
        user_id,
        COUNT(tweet_id) AS bucket
    FROM tweets
    WHERE tweet_date >= '2022-01-01'
      AND tweet_date < '2023-01-01'
    GROUP BY user_id
) AS total_tweets
GROUP BY bucket;

I submitted this solution on DataLemur and received a Solved verdict.

The upper bound < '2023-01-01' includes all of December 31, 2022.