Wawancara SQL: Melakukan Sessionize pada Event Pengguna dengan Batas Inaktivitas 30 Menit

Masalah dan Konteks yang Berlaku

Asumsikan tabel event PostgreSQL ini:

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  occurred_at timestamptz NOT NULL
);

Urutkan event setiap pengguna berdasarkan occurred_at dan event_id. Event pertama membuka sesi. Setiap event berikutnya membuka sesi baru saat jedanya dari event sebelumnya lebih besar dari atau sama dengan 30 menit. Kembalikan user_id, session_seq berbasis-satu, session_start, session_end, event_count, dan session_duration. Jeda tepat 30 menit memulai sesi baru; batas tersebut merupakan bagian dari kontrak.

Pola ini muncul dalam pemrosesan clickstream, analitik produk, dan funnel perilaku. Ini bukan masalah yang sama dengan mencari hari login berturut-turut. Daily streak membandingkan tanggal kalender yang berdekatan; sessionization membandingkan waktu yang berlalu antara event yang berdekatan untuk satu pengguna. Solusi utama menghitung hasil yang tepat atas kumpulan event yang lengkap dan terdeduplikasi secara logis. Kueri dengan batas rentang dan materialisasi inkremental memerlukan aturan batas tambahan.

Apa yang Sedang Dievaluasi oleh Pewawancara

Sinyal pertama adalah apakah kandidat mengubah deskripsi menjadi kontrak yang dapat dieksekusi. > 30 minutes dan >= 30 minutes menetapkan event batas secara berbeda. Membandingkan dengan event sebelumnya dan membandingkan dengan event pertama dalam sesi juga merupakan definisi yang berbeda. Permintaan ini menggunakan jeda antar event yang berdekatan, sehingga sesi yang terus aktif dapat berlangsung jauh lebih lama dari 30 menit.

Sinyal kedua adalah dekomposisi menjadi tiga tahap window: gunakan LAG() untuk membaca event sebelumnya, tandai batas sesi, dan jalankan SUM() kumulatif atas penanda tersebut. Perhitungan window yang diperlukan tidak bisa hanya disarangkan secara arbitrer dalam satu ekspresi PostgreSQL. CTE terpisah juga mengekspos setiap relasi perantara untuk inspeksi.

Sinyal ketiga adalah pengurutan deterministik. Dua event dapat memiliki occurred_at yang sama. Pengurutan berdasarkan waktu saja membiarkan urutan relatif keduanya tidak ditentukan. Menambahkan event_id yang unik memberikan LAG() dan running sum urutan total yang sama. Waktu yang berlalu di antara timestamp yang sama adalah nol, sehingga keduanya tetap berada dalam satu sesi.

Sinyal keempat adalah semantik waktu. timestamptz menunjukkan momen absolut yang cocok untuk perbandingan waktu yang berlalu. Mengonversi ke waktu lokal sebelum pengurangan dapat menyuntikkan lompatan daylight-saving. Konversi zona bernama dilakukan pada saat presentasi/tampilan, bukan dalam perhitungan batas sesi ini.

Terakhir, jawaban yang kuat mengenali bahwa data yang datang terlambat dapat menulis ulang riwayat. Event yang disisipkan di tengah linimasa dapat menghubungkan dua sesi yang sebelumnya terpisah. Oleh karena itu, sistem inkremental tidak dapat memperlakukan session_seq sebagai append-only; sistem memerlukan komputasi ulang berbatas, koreksi berversi, atau watermark finalitas yang eksplisit.

Pertanyaan untuk Diklarifikasi Sebelum Menjawab

• Sisi mana yang memiliki batas 30 menit? Permintaan ini memulai sesi baru pada >= 30 minutes. Aturan

produk yang menggunakan strictly-greater-than mengubah satu operator dan semua ekspektasi batas.

• Apakah kita membandingkan event yang berdekatan atau event pertama sesi? Event yang berdekatan di sini. Panjang

sesi maksimum memerlukan state terpisah yang dijangkarkan pada awal sesi.

• Bagaimana penanganan duplikat? event_id adalah kunci event logis, sehingga redelivery harus

dideduplikasi sebelum kueri ini. Event yang berbeda pada pengguna dan timestamp yang sama tetap merupakan baris yang valid.

• Zona waktu mana yang menentukan jeda? Waktu yang berlalu antara momen absolut. Zona waktu bernama mengubah

tampilan, bukan jumlah detik yang berlalu.

• Apakah kueri dibatasi waktu? Riwayat penuh bersifat langsung. Rentang pelaporan harus menentukan

apakah sesi yang dimulai sebelum rentang tetap utuh dan harus membaca konteks event sebelumnya.

• Seberapa lambat event dapat tiba? Kueri ad hoc dapat menghitung ulang. Hasil yang dimaterialisasi memerlukan

horizon koreksi, watermark, dan protokol pembaruan atau penarikan (retraction) ke downstream.

• Apa yang harus dikembalikan oleh tabel kosong atau pengguna dengan satu event? Nol baris untuk tabel kosong; satu

sesi berdurasi nol untuk pengguna dengan satu event.

Kerangka Jawaban 30 Detik

“Dalam setiap pengguna, saya membuat urutan deterministik berdasarkan occurred_at, event_id dan menggunakan LAG(occurred_at) untuk mendapatkan event sebelumnya. Saya menandai baris pertama dan setiap jeda minimal 30 menit sebagai 1, dengan semua baris lainnya ditandai 0. Jumlah kumulatif di atas frame ROWS eksplisit memberikan urutan sesi berbasis-satu. Saya kemudian melakukan pengelompokan berdasarkan pengguna dan urutan untuk menghitung waktu mulai, selesai, jumlah, dan durasi. Pengujian mencakup 29 menit 59 detik, tepat 30 menit, timestamp yang sama, pengguna satu event, ID duplikat, data terlambat, dan awal rentang pelaporan. Untuk materialisasi inkremental, saya menghitung ulang pengguna yang terpengaruh di dalam batas lateness yang diizinkan alih-alih mengasumsikan sesi hanya bersifat append.”

Pembahasan Mendalam Langkah demi Langkah

Pertama, dapatkan event sebelumnya dengan satu aturan pengurutan bersama. event_id tidak memengaruhi waktu yang berlalu; ini hanya menstabilkan timestamp yang sama:

WITH ordered AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    LAG(occurred_at) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
    ) AS previous_at
  FROM events
),
marked AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    CASE
      WHEN previous_at IS NULL THEN 1
      WHEN occurred_at - previous_at >= INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM ordered
),
sessionized AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    SUM(is_new_session) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_seq
  FROM marked
)
SELECT
  user_id,
  session_seq,
  MIN(occurred_at) AS session_start,
  MAX(occurred_at) AS session_end,
  COUNT(*) AS event_count,
  MAX(occurred_at) - MIN(occurred_at) AS session_duration
FROM sessionized
GROUP BY user_id, session_seq
ORDER BY user_id, session_seq;

Frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW yang eksplisit sangat penting. Running total harus menyerap penanda batas satu baris fisik pada satu waktu daripada mewarisi semantik peer dari window frame default. event_id yang unik menghilangkan peer dalam kueri ini, namun menyatakan frame mengunci maksud query dan mencegah penghapusan tie-breaker di masa mendatang mengubah perilaku secara diam-diam.

Gunakan event ini untuk memeriksa batas:

Pengguna 1 memiliki dua sesi: dua event selama lima menit, kemudian tiga event selama 35 menit. Total rentang Pengguna 2 adalah 58 menit, namun setiap jeda yang berdekatan di bawah 30 menit, sehingga ketiga event tetap berada dalam satu sesi. Ini membedakan sessionization jeda berdekatan dari durasi sesi maksimum.

Kebenaran logika berasal dari invarian sederhana. Baris pertama untuk seorang pengguna menaikkan running sum dari nol menjadi satu. Setelah itu, hanya baris yang memenuhi predikat batas yang meningkatkan sum; setiap baris non-batas mempertahankannya. Dua baris memiliki nilai kumulatif yang sama tepat ketika tidak ada batas bertanda yang memisahkan keduanya. Oleh karena itu, pengelompokan berdasarkan pengguna dan nilai tersebut menghasilkan setiap segmen kontigu maksimal tanpa menggabungkan pengguna atau melintasi batas.

Untuk N event, rencana window biasanya mengurutkan berdasarkan pengguna dan waktu, menghasilkan waktu O(N log N); pemindaian window dan agregasi adalah O(N). State perantara adalah O(N) dan dapat tumpah (spill). Indeks dapat mencocokkan urutan logis:

CREATE INDEX events_session_order_idx
  ON events (user_id, occurred_at, event_id);

Indeks tidak menjamin rencana bebas pengurutan (sort-free). Biaya pemindaian penuh, visibilitas, paralelisme, dan predikat semuanya memengaruhi optimizer. Periksa scan, sort, temporary I/O, perkiraan baris, dan baris aktual dengan EXPLAIN (ANALYZE, BUFFERS) pada data yang representatif alih-alih menyatakan keberhasilan hanya dari keberadaan indeks.

Pemfilteran rentang waktu adalah jebakan kebenaran yang paling mudah terjadi. Jika kueri dimulai pada 10:00 sementara pengguna memiliki event pada 09:50 dan 10:10, pemfilteran terlebih dahulu secara keliru menandai 10:10 sebagai sesi baru. Jika output hanya memerlukan keanggotaan di dalam rentang, baca setidaknya event sebelumnya yang paling dekat sebelum awal rentang untuk setiap pengguna, lalu kecualikan baris konteks dari output. Jika output harus menyertakan awal sesi yang lengkap, lanjutkan membaca ke belakang hingga mencapai batas 30 menit yang asli.

Data yang datang terlambat juga dapat menggabungkan riwayat. Event pada 09:00 dan 09:50 awalnya membentuk sesi terpisah. Event terlambat pada 09:25 mengubah kedua jeda yang berdekatan menjadi 25 menit dan menggabungkannya. Pemrosesan batch dapat menghitung ulang partisi yang terpengaruh. Sistem inkremental harus menghitung ulang per pengguna di atas horizon lateness yang diizinkan dan memublikasikan versi atau penarikan (retraction). Kontrak harus menyatakan apakah data di luar watermark dikarantina, dibuang, atau diizinkan untuk memicu koreksi yang lebih luas.

Validasi setiap tahap. Setiap previous_at non-pertama di ordered harus sama dengan timestamp sebelumnya dalam urutan bersama. marked berisi 1 hanya pada baris pertama dan batas ambang. Untuk setiap pengguna, session_seq dimulai dari satu, tidak pernah berkurang, dan bertambah paling banyak satu. Relasi akhir harus memiliki session_start <= session_end; jumlah event-nya harus sama dengan jumlah event input logis; dan jeda antar sesi yang berdekatan harus minimal 30 menit.

Contoh Jawaban Berkualitas Tinggi

“Saya pertama-tama akan mengonfirmasi bahwa batasnya lebih besar dari atau sama dengan 30 menit dan bahwa perbandingan menggunakan event yang berdekatan. event_id yang unik mendeduplikasi event logis, sementara event yang berbeda pada waktu yang sama tetap dipertahankan. CTE pertama mengurutkan setiap pengguna berdasarkan waktu dan ID event serta menggunakan LAG untuk timestamp sebelumnya. CTE kedua menandai baris pertama dan setiap jeda ambang batas. CTE ketiga mengambil running sum atas frame ROWS eksplisit untuk membuat urutan sesi yang stabil. Saya kemudian mengagregasi berdasarkan pengguna dan urutan untuk waktu mulai, selesai, jumlah, dan durasi.

Saya akan menguji 29 menit 59 detik dan tepat 30 menit, timestamp yang sama, pengguna satu event, dan tabel kosong. Saya juga akan menguji event sebelumnya sebelum rentang pelaporan, karena memfilter sebelum LAG membuat batas sesi palsu. Pengurutan mendominasi pada sekitar O(N log N). Indeks pada (user_id, occurred_at, event_id) dapat memberikan urutan yang diperlukan, tetapi keputusannya berasal dari rencana eksekusi yang memperhatikan buffer.

Untuk hasil yang dimaterialisasi secara kontinu, saya tidak akan memperlakukan nomor sesi sebagai tidak dapat diubah (immutable). Event pada 09:00 dan 09:50 terpisah sampai event terlambat pada 09:25 menjembatani keduanya. Sistem membutuhkan komputasi ulang bercakup-pengguna di dalam window lateness dan konsumen yang sadar-versi. Event di luar watermark harus mengikuti kebijakan karantina eksplisit atau koreksi yang lebih luas.”

Kesalahan Umum

• Mengurangkan event pertama sesi dari event saat ini → Pengguna yang terus aktif akan terpecah

setelah durasi total melebihi 30 menit → bandingkan event yang berdekatan seperti yang ditentukan.

• Mempertahankan jeda tepat 30 menit di sesi lama → Ini melanggar kontrak >= 30 minutes →

tulis pengujian ambang batas khusus.

• Hanya mengurutkan berdasarkan occurred_at → Timestamp yang sama tidak memiliki urutan total yang stabil → **tambahkan

event_id yang unik dan gunakan kembali urutan tersebut di kedua window.**

• Menghilangkan frame ROWS yang eksplisit → Perilaku peer default mungkin berbeda dari akumulasi baris demi baris →

nyatakan frame dari baris pertama hingga baris saat ini.

• Mengurangkan waktu lokal (wall time) → Lompatan daylight-saving membuat atau menyembunyikan satu jam → **bandingkan

momen timestamptz dan lokalisasi hanya untuk tampilan.**

• Memfilter pada awal laporan sebelum LAG → Event pertama dalam rentang kehilangan event sebelumnya dan

menjadi batas palsu → baca konteks pra-batas sebelum memotong output.

• Menghitung redelivery sebagai event → event_count tergelembungkan → deduplikasi berdasarkan kunci event logis.
• Mengasumsikan sesi historis hanya bersifat append → Data terlambat dapat memindahkan batas atau menggabungkan sesi →

hitung ulang rentang berbatas dan publikasikan hasil yang dapat dikoreksi.

• Mengasumsikan indeks yang cocok menghilangkan setiap pengurutan → Optimizer dapat memilih rencana lain berdasarkan

biaya pemindaian dan predikat → periksa rencana representatif dan temporary I/O.

Pertanyaan Lanjutan dan Jawaban

Lanjutan 1: Apa yang berubah jika jeda tepat 30 menit tetap berada di sesi lama?

Ubah predikat batas dari >= INTERVAL '30 minutes' menjadi > INTERVAL '30 minutes'. Sisa dari pipeline window tidak berubah, tetapi definisi metrik dan setiap data uji harus berubah bersamanya. Simpan kasus pengujian untuk 29:59, 30:00, dan 30:01 agar kebijakan tidak bergeser di kemudian hari.

Lanjutan 2: Apakah solusi running-sum cukup jika sesi dapat berlangsung paling lama dua jam?

Tidak. Jeda kecil yang berdekatan dapat memperpanjang sesi tanpa batas, sehingga batas berikutnya juga bergantung pada awal sesi dinamis. CTE rekursif, ordered state machine, atau state per-pengguna dalam stream processor biasanya lebih jelas. Pertama, bedakan window tetap dua jam dari “dua jam setelah event pertama sesi”; itu adalah kontrak yang berbeda.

Lanjutan 3: Bagaimana Anda mengoreksi hasil yang dimaterialisasi untuk event yang tiba 24 jam terlambat?

Cari event berdasarkan user_id, baca rentang yang mencakup setidaknya satu batas terkonfirmasi di kedua sisi timestamp yang terlambat, hitung ulang segmen tersebut, dan bandingkan perbedaannya (diff) dengan versi sebelumnya. Output membutuhkan kunci bisnis dan versi yang stabil sehingga konsumen dapat menerapkan pembaruan, penggabungan, dan penarikan secara idempoten. Jika watermark melarang pengubahan output berusia 24 jam, karantina event tersebut dan ekspos sinyal kualitas data alih-alih mengabaikannya secara diam-diam.

Lanjutan 4: Bagaimana Anda mengoptimalkan ini untuk miliaran event?

Baca partisi waktu yang dapat dipangkas (pruned) dan manfaatkan urutan (user_id, occurred_at, event_id) untuk mengurangi pengurutan. Pekerjaan berkala membawa event terakhir setiap pengguna dan status sesi terbuka melintasi batas partisi sehingga batas file atau tanggal tidak menjadi batas sesi. Validasi desain dengan user skew, sort spill, byte yang dipindai, dan latensi end-to-end yang nyata. Hotspot super-user tunggal mungkin memerlukan jalur berurutan khusus.

Lanjutan 5: Bagaimana Anda membuktikan bahwa tidak ada event yang terlewat atau dihitung dua kali?

Gunakan pemeriksaan konservasi. Jumlah nilai akhir event_count harus sama dengan jumlah baris input yang terdeduplikasi. Setiap event_id dipetakan ke tepat satu (user_id, session_seq). Urutan sesi dimulai dari satu dan bertambah secara bersambung per pengguna. Jeda yang berdekatan di dalam sesi berada di bawah 30 menit, sedangkan batas antar sesi minimal 30 menit. Kemudian jalankan uji properti dengan input yang diacak, redelivery, timestamp yang sama, tepi partisi, dan penggabungan event terlambat.