Wawancara SQL: Menghitung Rolling 7-Day Active Users

Prompt dan Konteks yang Berlaku

Sebuah tabel analitik memuat events(event_id, user_id, event_at, event_name, is_internal_user). event_at adalah timestamptz di PostgreSQL. Seorang pengguna dianggap aktif setelah melakukan setidaknya satu event app_open, view_dashboard, atau run_report. Pengguna internal tidak dihitung.

Kembalikan satu baris untuk setiap tanggal kalender America/New_York dari 1 Juni hingga 30 Juni 2026. Untuk setiap tanggal laporan, rolling_7d_active_users adalah jumlah distinct eligible user yang aktif pada tanggal tersebut atau pada salah satu dari enam tanggal kalender sebelumnya. Oleh karena itu, 1 Juni memerlukan aktivitas dari 26 Mei hingga 1 Juni, inklusif. Pengguna dengan dua puluh event pada tiga tanggal berbeda tetap dihitung satu kali dalam window tersebut.

Kueri harus mempertahankan tanggal dengan nol pengguna, menggunakan batasan waktu yang eksplisit, dan menyatakan bagaimana late-arriving event memengaruhi hasil yang sudah dipublikasikan sebelumnya. Tantangan utamanya adalah rolling distinct union. Ini bukan merupakan rolling sum dari hitungan harian yang sudah diagregasikan sebelumnya.

Hal yang Dievaluasi oleh Pewawancara

Sinyal pertama adalah definisi metrik sebelum sintaks. Kandidat yang kuat menyatakan qualifying event, populasi yang dikecualikan, zona waktu pelaporan, grain output, window tujuh tanggal inklusif, dan batas kelengkapan data. Tanpa hal-hal tersebut, dua kueri yang valid secara sintaksis bisa menjawab dua pertanyaan yang berbeda.

Sinyal kedua adalah kontrol grain. Raw event pertama-tama harus diubah menjadi pasangan (user_id, activity_date) yang unik. Langkah ini menghapus duplikat pada hari yang sama tetapi sengaja mempertahankan seorang pengguna di beberapa tanggal. Window terakhir kemudian menghitung distinct union dari kumpulan pengguna tersebut.

Sinyal ketiga adalah menolak jalan pintas yang tampak menggoda. Menjumlahkan tujuh nilai DAU akan menghitung seorang pengguna satu kali per tanggal aktif. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW mendeskripsikan tujuh baris, belum tentu tujuh tanggal kalender, dan menerapkannya pada hitungan harian tetap tidak dapat merekonstruksi distinct union lintas hari.

Sinyal terakhir adalah pertimbangan produksi (production judgment): memindai warm-up enam hari sebelum rentang yang diminta, mempertahankan tanggal kosong dengan calendar spine, mengonversi timestamp menggunakan zona waktu yang dideklarasikan, mendefinisikan semantik refresh late-event, dan memilih strategi penskalaan eksak atau aproksimasi secara matang.

Pertanyaan untuk Diklarifikasi Sebelum Menjawab

Apa yang memenuhi syarat sebagai aktif? Definisi yang hanya berbasis login menghasilkan kumpulan yang berbeda dari

product event yang bermakna. Nama event dan pengecualian bot atau pengguna internal termasuk dalam kontrak metrik.

Zona waktu mana yang menentukan suatu hari? Jawaban ini menggunakan America/New_York. UTC akan memindahkan event di dekat

tengah malam lokal ke tanggal laporan yang berbeda.

Apakah window-nya tujuh tanggal kalender atau 168 jam yang berlalu? Prompt meminta tanggal kalender lokal.

Transisi daylight-saving dapat membuat tujuh tanggal tersebut berisi 167 atau 169 jam yang berlalu.

Apakah kedua batas bersifat inklusif? Kumpulan pengguna mencakup report_date - 6 hingga report_date.

Filter timestamp sumber menggunakan rentang half-open untuk menghindari penghitungan ganda pada tengah malam berikutnya.

Haruskah tanggal yang hilang tetap muncul? Ya. Buat semua tiga puluh tanggal laporan alih-alih memperoleh tanggal hanya

dari event yang ada.

Seberapa lengkap tabel event? Jika event dapat tiba terlambat tiga hari, hasil terkini bersifat

sementara atau memerlukan watermark yang dinyatakan secara jelas. SQL saja tidak dapat membuat input yang tidak lengkap menjadi final.

Apakah distinct eksak wajib digunakan? Kueri wawancara ini bersifat eksak. Pada skala yang sangat besar, representasi

mergeable-set aproksimasi mungkin dapat diterima hanya setelah kontrak toleransi kesalahannya (error contract) disetujui.

Database dan skala apa yang berlaku? Jawaban ini menggunakan PostgreSQL. Data warehouse mungkin menggunakan fungsi date-spine

lain atau primitif bitmap sambil tetap mempertahankan semantik set yang sama.

Kerangka Jawaban 30 Detik

“Pertama-tama, saya akan mendefinisikan active event, pengecualian, America/New_York sebagai batas hari, dan grain output berupa satu baris per tanggal laporan. Saya akan memindai mulai dari 26 Mei karena 1 Juni membutuhkan enam tanggal sebelumnya, mengonversi timestamp ke tanggal lokal, dan melakukan deduplikasi menjadi satu baris per pengguna per tanggal. Kemudian saya akan menghasilkan rentang 1 Juni hingga 30 Juni dengan generate_series, melakukan left join setiap tanggal ke aktivitas dari date - 6 hingga tanggal tersebut, dan menghitung distinct users.

Saya tidak akan menjumlahkan daily active users karena pengguna yang aktif di beberapa hari akan terhitung berulang kali. Window enam baris juga gagal jika ada tanggal yang hilang dan tidak membentuk distinct union. Saya akan menguji batas tengah malam, duplikat, tanggal kosong, dan aktivitas warm-up, lalu memublikasikan watermark as-of atau memperbarui (refresh) tanggal-tanggal terkini saat late event tiba.”

Pembahasan Mendalam Langkah demi Langkah

Langkah 1: Tetapkan kontrak metrik dan rentang input

Output yang diminta dimulai pada 1 Juni, tetapi pemindaian sumber dimulai pada 26 Mei. Hanya membaca baris bulan Juni akan menyebabkan undercount pada enam tanggal laporan pertama. Batas atas sumber adalah tengah malam lokal 1 Juli; aktivitas di masa mendatang tidak relevan untuk trailing window yang berakhir pada 30 Juni.

Konversikan tengah malam lokal tersebut menjadi konstanta timestamptz di dalam filter. Ini menjaga predikat tetap berada pada kolom event_at yang terindeks. Melakukan type casting setiap timestamp sumber ke date di dalam klausa WHERE dapat mencegah index range biasa melakukan pruning yang berguna.

Langkah 2: Normalisasi event menjadi distinct user-days

Konversikan setiap instan yang memenuhi syarat ke tanggal kalender New York hanya setelah menerapkan filter timestamp yang berbatas. Kemudian lakukan group by berdasarkan user_id dan tanggal lokal. Percobaan ulang event (retry) dengan row ID berbeda serta dua puluh event dari satu pengguna dalam satu hari semuanya menjadi satu user-day.

Deduplikasi ini tidak menyelesaikan masalah akhir secara otomatis. Jika pengguna yang sama aktif pada 1 Juni dan 2 Juni, kedua baris user-day tersebut harus tetap tersedia sehingga trailing window untuk salah satu dari kedua tanggal tersebut dapat menyertakan pengguna tersebut.

Langkah 3: Buat date spine yang lengkap

generate_series menghasilkan setiap tanggal laporan secara independen dari keberadaan event. Memulai langsung dari tabel event akan melewatkan tanggal yang kosong, mengubah jumlah baris dalam frame ROWS, dan tidak menyisakan titik dashboard bernilai nol. Spine merupakan grain output yang otoritatif.

Langkah 4: Hitung distinct union untuk setiap window

Kueri eksak langsungnya adalah:

sql

WITH params AS (
  SELECT
    DATE '2026-06-01' AS report_start,
    DATE '2026-06-30' AS report_end
),
activity_days AS (
  SELECT
    e.user_id,
    (e.event_at AT TIME ZONE 'America/New_York')::date AS activity_date
  FROM events AS e
  WHERE e.event_at >= TIMESTAMPTZ '2026-05-26 00:00:00 America/New_York'
    AND e.event_at <  TIMESTAMPTZ '2026-07-01 00:00:00 America/New_York'
    AND e.event_name IN ('app_open', 'view_dashboard', 'run_report')
    AND e.is_internal_user = false
  GROUP BY
    e.user_id,
    (e.event_at AT TIME ZONE 'America/New_York')::date
),
report_dates AS (
  SELECT gs::date AS report_date
  FROM params AS p
  CROSS JOIN generate_series(
    p.report_start,
    p.report_end,
    INTERVAL '1 day'
  ) AS gs
)
SELECT
  d.report_date,
  COUNT(DISTINCT a.user_id) AS rolling_7d_active_users
FROM report_dates AS d
LEFT JOIN activity_days AS a
  ON a.activity_date BETWEEN d.report_date - 6 AND d.report_date
GROUP BY d.report_date
ORDER BY d.report_date;

Left join mempertahankan tanggal laporan yang kosong. COUNT(DISTINCT a.user_id) mengabaikan nilai null yang dihasilkan oleh left join yang tidak cocok. Interval tersebut berisi tepat tujuh nilai date: tanggal saat ini dan enam pendahulunya.

Langkah 5: Buktikan mengapa shortcut window umum gagal

Misalkan pengguna A aktif pada hari Senin dan Selasa, sedangkan pengguna B aktif hanya pada hari Selasa. DAU masing-masing adalah 1 dan 2, tetapi distinct union dua hari adalah 2, bukan 3. Begitu hitungan harian menggantikan identitas pengguna, SQL tidak dapat mengetahui bahwa A muncul di kedua hari tersebut.

Row frame memicu kesalahan lain. Jika hari Rabu tidak memiliki event dan tidak ada dalam input, “enam baris sebelumnya” dapat menjangkau delapan atau lebih tanggal kalender ke belakang. Date spine memang memperbaiki jeda kalender, tetapi rolling sum atas DAU tetap menghitung ganda identitas pengguna. Operasi yang benar adalah union terlebih dahulu, kemudian kardinalitas.

Langkah 6: Melakukan penskalaan tanpa mengubah semantik

Untuk rentang moderat, indeks data mentah berdasarkan event_at dan reduksi menjadi user-days sebelum join interval 7x. Tabel aktivitas harian yang dimaterialisasi dengan kunci (activity_date, user_id) menghindari pemindaian ulang raw event. Partition pruning harus mencakup tanggal-tanggal warm-up.

Untuk rentang laporan yang lebih panjang, ekspansikan setiap user-day ke paling banyak tujuh tanggal laporan yang memenuhi syarat, batasi tanggal-tanggal tersebut ke rentang yang diminta, lalu kelompokkan distinct users. Ini mengubah bentuk join tetapi tidak mengubah ekspansi kasus terburuk sebesar 7x. Engine dengan exact bitmap set dapat menggabungkan (union) bitmap pengguna harian; sketch aproksimasi harus mendukung set union dan harus memperlihatkan measured error. Menjumlahkan estimasi HyperLogLog harian tidaklah valid karena kardinalitas aproksimasi tidak dapat dijumlahkan untuk mendapatkan sebuah union.

Langkah 7: Tentukan penanganan late data dan verifikasi

Publikasikan watermark as_of bersamaan dengan hasilnya. Jika pipeline menerima event yang terlambat hingga tiga hari, perbarui setidaknya setiap tanggal laporan yang window input tujuh harinya beririsan dengan data yang dapat berubah tersebut. ID event yang stabil membantu deduplikasi saat penyerapan (ingestion), sementara pengelompokan user-day melindungi metrik ini dari beberapa qualifying event; tidak ada satu pun dari keduanya yang dapat menggantikan pemantauan kelengkapan data.

Gunakan data uji acuan (oracle) manual yang memuat event duplikat, pengguna yang sama di beberapa tanggal, pengguna internal, event yang tidak memenuhi syarat, event warm-up 26 Mei dan 31 Mei, tanggal kosong, instan di kedua sisi tengah malam lokal, serta batas daylight-saving. Bandingkan setiap tanggal output dengan set union sederhana di tingkat aplikasi. Jalankan EXPLAIN (ANALYZE, BUFFERS) pada volume data yang menyerupai produksi dan verifikasi source pruning, kardinalitas user-day, ekspansi join, runtime, serta perilaku spill.

Contoh Jawaban yang Kuat

“Saya akan mendefinisikan set tersebut sebelum menulis SQL: pengguna yang eligible memiliki setidaknya satu product event yang disetujui, pengguna internal dikecualikan, dan satu hari berarti America/New_York. Untuk tanggal laporan D, set tersebut adalah setiap pengguna eligible dengan tanggal aktivitas lokal antara D minus enam dan D, inklusif. Output harus memuat seluruh tiga puluh tanggal.

Saya akan memfilter timestamp mentah dari tengah malam lokal 26 Mei hingga tengah malam lokal 1 Juli, dengan menjaga batas atas tetap eksklusif. Setelah pemfilteran, saya mengonversinya ke tanggal lokal dan melakukan group by berdasarkan pengguna dan tanggal. Spine generate_series menyediakan rentang 1 Juni hingga 30 Juni. Setiap tanggal spine melakukan left join dengan user-days pada trailing interval-nya, dan COUNT(DISTINCT user_id) mengembalikan kardinalitas union.

Saya akan menolak SUM(DAU) karena identitas yang aktif di beberapa tanggal akan berulang, dan menolak ROWS 6 PRECEDING karena baris bukanlah tanggal kalender dan agregasi harian membuang identitas pengguna. Untuk skalabilitas, saya akan mematerialisasi (activity_date, user_id), melakukan pruning pada rentang warm-up, dan mempertimbangkan exact bitmap union atau measured approximate set union hanya jika output eksak tidak diwajibkan. Hasilnya menyertakan watermark as_of, dan late data akan memicu komputasi ulang yang terikat.”

Kesalahan Umum

Menjumlahkan tujuh nilai DAU → pengguna berulang dihitung satu kali per tanggal aktif → **gabungkan (union) identitas pengguna

di sepanjang window, lalu hitung.**

Menggunakan ROWS 6 PRECEDING pada tanggal yang renggang (sparse) → enam baris dapat mencakup lebih dari enam tanggal sebelumnya → **buat

calendar spine yang lengkap dan nyatakan batasan kalender secara eksplisit.**

Hanya memindai bulan Juni → window awal Juni kehilangan aktivitas bulan Mei → sertakan warm-up enam hari.
Melakukan type casting event_at pada filter sumber → indeks timestamp biasa mungkin tidak melakukan pruning secara efisien →

filter dengan batas half-open timestamptz sebelum menurunkan tanggal lokal.

Menghitung raw events → percobaan ulang (retry) dan penggunaan berulang menggelembungkan jumlah pengguna → **lakukan deduplikasi menjadi user-day dan tetap

hitung distinct di sepanjang window akhir.**

Menurunkan tanggal output dari event → tanggal kosong akan menghilang → jadikan date spine sebagai grain output.
Menyebut output terkini sebagai data final → late event dapat mengubah set → **publikasikan watermark dan perbarui

window yang terdampak.**

Menjumlahkan kardinalitas aproksimasi harian → penjumlahan kardinalitas tidak dapat menghilangkan irisan data → **gabungkan (merge)

sketch atau bitmap yang mendukung set sebelum mengestimasi union.**

Pertanyaan Lanjutan dan Jawabannya

Pertanyaan Lanjutan 1: Apa yang berubah jika metrik ini diartikan sebagai 168 jam sebelumnya?

Bandingkan titik waktu instan alih-alih nilai date lokal. Untuk setiap instan laporan, gunakan window timestamp half-open seperti (report_at - interval '168 hours', report_at], dengan batas produk yang dinyatakan secara tepat. Di sekitar transisi daylight-saving, nilai ini berbeda dari tujuh tanggal kalender New York. Jangan menamai satu definisi dengan definisi lainnya.

Pertanyaan Lanjutan 2: Bisakah window function menyelesaikan exact rolling distinct dengan sendirinya?

Window aggregate berguna ketika agregat dibentuk dari nilai-nilai baris, seperti rolling sum. Di sini, state yang diperlukan adalah sebuah set dengan penghapusan ketika tanggal lama keluar dari window. Jawaban langsung di PostgreSQL adalah mempertahankan identitas pengguna dan menggabungkannya ke tanggal laporan. Engine khusus mungkin menyediakan windowing exact bitmap-union, tetapi itu adalah fitur engine, bukan alasan untuk menjumlahkan hitungan harian.

Pertanyaan Lanjutan 3: Bagaimana cara melakukan refresh setelah suatu event tiba terlambat tiga hari?

Cari tanggal aktivitas lokal event tersebut, yaitu A. Event ini dapat memengaruhi tanggal laporan A hingga A plus enam, yang beririsan dengan rentang yang dipublikasikan. Hitung ulang atau ganti hanya partisi-partisi tersebut, majukan watermark setelah rekonsiliasi, dan jaga agar operasi tetap idempoten. Hanya memperbarui tanggal A akan melewatkan enam window di hilir.

Pertanyaan Lanjutan 4: Bagaimana Anda melakukan segmentasi berdasarkan negara?

Pertama, tentukan apakah negara merupakan atribut dari event, profil pengguna saat ini, atau profil yang berubah perlahan (slowly changing profile) pada saat aktivitas terjadi. Pilihan tersebut mengubah kebenaran historis. Tambahkan negara yang dipilih ke grain user-day, date spine, pengelompokan, dan oracle validasi. Join ke profil saat ini dapat menulis ulang riwayat ketika seorang pengguna berpindah negara.

Pertanyaan Lanjutan 5: Bagaimana jika distinct eksak terlalu memakan resource?

Ukur kueri eksak terlebih dahulu. Jika batas toleransi error (error budget) yang disetujui mengizinkan aproksimasi, simpan satu mergeable set sketch per tanggal aktivitas dan segmen, gabungkan tujuh sketch, lalu lakukan estimasi satu kali. Validasi bias dan relative error terhadap set eksak untuk slice dengan kardinalitas rendah, normal, dan tinggi. Pertahankan pemrosesan eksak untuk penagihan (billing), kelayakan (eligibility), atau keputusan lain yang tidak mentoleransi kesalahan estimasi.