Back to questions
This is the same question as problem #6 in the SQL Chapter of Ace the Data Science Interview!
Assume you're given a table Twitter tweet data, write a query to obtain a histogram of tweets posted per user in 2022. Output the tweet count per user as the bucket and the number of Twitter users who fall into that bucket.
In other words, group the users by the number of tweets they posted in 2022 and count the number of users in each group.
|214252||111||Am considering taking Tesla private at $420. Funding secured.||12/30/2021 00:00:00|
|739252||111||Despite the constant negative press covfefe||01/01/2022 00:00:00|
|846402||111||Following @NickSinghTech on Twitter changed my life!||02/14/2022 00:00:00|
|241425||254||If the salary is so competitive why won’t you tell me what it is?||03/01/2022 00:00:00|
|231574||148||I no longer have a manager. I can't be managed||03/23/2022 00:00:00|
Based on the example output, there are two users who posted only one tweet in 2022, and one user who posted two tweets in 2022. The query groups the users by the number of tweets they posted and displays the number of users in each group.
The dataset you are querying against may have different input & output - this is just an example!
First, we need to find the number of tweets posted by each user in 2022 by grouping the tweet records by user ID and counting the tweets.
The output shows the number of tweets posted by each user in 2022:
Based on the output, we can infer that in the year 2022, user 111 has posted two tweets, while users 148 and 254 have only posted one tweet each.
Next, we use the query above as a subquery, then we use the field as the tweet bucket and retrieve the number of users.
This query generates a histogram of the number of tweets per user in 2022. The output shows the tweet count per user as the tweet bucket and the number of Twitter users who fall into that bucket.
Alternatively, we can use a Common Table Expression (CTE) instead of a subquery to compute the tweet counts.
A CTE is a data set that is created temporarily and can be used within a query. It is available for use during the entire session of the query execution. On the other hand, a subquery is a query nested within another query and can only be used within that query. A subquery typically acts as a column with a single value in the FROM or WHERE clause.
The benefits of using a CTE are that it is more readable and can be reused throughout the query session, whereas a subquery can only be used within the query in which it is defined.
Solution #2: Using CTE