[PostgreSQL] (Easy) Histogram of Tweets [Twitter SQL Interview Question]

SELECT counts as tweet_bucket
    , COUNT(user_id) as users_num
FROM (
  SELECT COUNT(tweet_id) AS counts
      , user_id
  FROM tweets
  WHERE EXTRACT(YEAR FROM tweet_date) = 2022
  GROUP BY user_id) sub
GROUP BY counts
ORDER BY tweet_bucket;

 

mysql:

WHERE YEAR(tweet_date) == 2022

 

postgresql:

WHERE EXTRACT(YEAR FROM tweet_date) = 2022

 

count(*) 말고 count(col)을 하면 널값을 제외하고 셈

 

 

출처

https://datalemur.com/questions/sql-histogram-tweets