라이브 코딩테스트 뽀개기 Day1: Histogram of Tweets

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.

tweets Table: 

Column	NameType
tweet_id	integer
user_id	integer
msg	string
tweet_date	timestamp

tweets Example Input:

tweet_id	user_id	msg	tweet_date
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

Example Output:

tweet_bucket	users_num
1	2
2	1

Explanation:

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!

 

서브쿼리를 사용하는 방법

SELECT tweet_bucket, 
  COUNT(user_id) AS users_num 
FROM (
  SELECT user_id, 
    COUNT(tweet_id) AS tweet_bucket 
  FROM tweets 
  WHERE tweet_date BETWEEN '2022-01-01' 
    AND '2022-12-31' 
  GROUP BY user_id) AS total_tweets 
GROUP BY tweet_bucket;

1. total_tweets라는 서브쿼리를 만들어 user_id별 트윗 개수를 구한다.
2. 트윗 개수별(tweet_bucket) 유저 수를 계산해 특정 트윗 개수를 가진 유저가 몇 명인지 출력한다. 

CTE를 사용하는 방법

• CTE란? 임시적으로 사용할 수 있는 테이블을 생성하는 SQL 구문

WITH total_tweets AS (
  SELECT user_id, 
    COUNT(tweet_id) tweet_bucket
  FROM tweets
  WHERE tweet_date BETWEEN '2022/01/01'
    AND '2023/01/01'
  GROUP BY user_id
)
SELECT tweet_bucket, 
  COUNT(user_id) users_num
FROM total_tweets
GROUP BY tweet_bucket;