SQL: Solving Histogram of Tweets

Catatan singkat penyelesaian SQL untuk Histogram of Tweets.

Soal ini bersumber dari DataLemur — Histogram of Tweets.

Deskripsi soal

Dari tabel tweets, hitung berapa banyak pengguna yang membuat satu tweet, dua tweet, dan seterusnya selama 2022. Hasilnya berupa tweet_bucket (jumlah tweet) dan users_num (jumlah pengguna).

Struktur tabel

Nama kolom	Tipe
tweet_id	integer
user_id	integer
msg	string
tweet_date	timestamp

Contoh input

Tanggal ditampilkan tanpa jam agar lebih ringkas:

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

Contoh output

tweet_bucket	users_num
1	2
2	1

Pengguna 254 dan 148 masing-masing membuat satu tweet selama 2022, sedangkan pengguna 111 membuat dua tweet. Tweet dari Desember 2021 tidak ikut dihitung.

Pendekatan

Awalnya aku berpikir: sebelum menghitung pengguna yang jumlah tweet-nya sama, aku perlu tahu dulu berapa tweet yang dibuat setiap pengguna. Jadi, aku filter datanya ke tahun 2022, lalu memakai GROUP BY user_id dan COUNT(tweet_id). Bagian ini menjadi inner query: hasilnya satu baris per pengguna, dengan jumlah tweet disimpan sebagai bucket.

Hasil tadi kemudian bisa dianggap sebagai tabel baru. Kalau dua pengguna masing-masing membuat satu tweet, keduanya punya baris dengan bucket = 1. Di outer query, aku tinggal mengelompokkan berdasarkan bucket, lalu memakai COUNT(user_id) untuk menghitung penggunanya. Jadi, dua query ini menjawab dua pertanyaan secara berurutan: setiap pengguna membuat berapa tweet, lalu ada berapa pengguna dengan jumlah tweet tersebut?

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;

Solusi ini sudah aku submit di DataLemur dan mendapat verdict Solved.

Batas < '2023-01-01' memastikan seluruh 31 Desember 2022 ikut terhitung.