Analytical Query

Tim produk tidak bertanya “berapa total penjualan?” — mereka bertanya “dari 10.000 pengunjung, berapa yang sampai membayar?”, “pengguna yang mendaftar bulan November masih aktif berapa persen bulan ini?”, dan “pelanggan mana yang mulai menjauh?”. Jawaban-jawaban itu tidak datang dari satu SELECT; datang dari pipeline query bertingkat: agregasi kondisional per pengguna, pivot grid kohort, pembucketan ntile, dan penomoran sesi dari jeda waktu. Bab ini membangun keempatnya di atas satu dataset event web Toko Maju Jaya yang sama, memperlihatkan tabel antara setiap tahap, lalu menutup tiap pola dengan membaca hasilnya seperti seorang analis membaca laporan. Semua tanggal dibekukan pada November 2025–Februari 2026 agar setiap angka dapat ditelusuri ulang.

Dataset: Pengguna dan Log Event Web #

Tabel pengguna mencatat tanggal daftar — bahan pembentuk kohort. Tabel event_web menyimpan empat jenis event yang membentuk funnel: page_view, add_to_cart, checkout, dan purchase (dengan nilai rupiah pada pembelian). Sepuluh pengguna dibagi perannya: enam pembeli dengan frekuensi berbeda, Andi yang berhenti di checkout, Joko yang berhenti di keranjang, serta Tono dan Mira yang hanya sekadar melihat:

sql
buat-tabel.sql
-- Tabel latihan chapter Analytical Query: pengguna dan log event web
-- Toko Maju Jaya, November 2025 sampai Februari 2026 (tanggal tetap).
-- Satu baris event_web = satu aksi; jenis bertingkat funnel:
-- page_view -> add_to_cart -> checkout -> purchase.
CREATE TABLE pengguna (
    id            integer PRIMARY KEY,
    nama          text NOT NULL,
    email         text NOT NULL,
    tanggal_daftar date NOT NULL
);

CREATE TABLE event_web (
    id           integer PRIMARY KEY,
    pengguna_id  integer NOT NULL REFERENCES pengguna (id),
    jenis        text NOT NULL,
    waktu        timestamp NOT NULL,
    nilai        numeric(12,2) NOT NULL
);

INSERT INTO pengguna (id, nama, email, tanggal_daftar) VALUES
    ( 1, 'Budi',  '[email protected]',  DATE '2025-11-03'),
    ( 2, 'Siti',  '[email protected]',  DATE '2025-11-10'),
    ( 3, 'Andi',  '[email protected]',  DATE '2025-12-01'),
    ( 4, 'Rina',  '[email protected]',  DATE '2025-12-15'),
    ( 5, 'Tono',  '[email protected]',  DATE '2026-01-05'),
    ( 6, 'Wati',  '[email protected]',  DATE '2026-01-20'),
    ( 7, 'Joko',  '[email protected]',  DATE '2026-02-02'),
    ( 8, 'Mira',  '[email protected]',  DATE '2026-02-10'),
    ( 9, 'Dewi',  '[email protected]',  DATE '2025-11-28'),
    (10, 'Fajar', '[email protected]', DATE '2025-12-08');

INSERT INTO event_web (id, pengguna_id, jenis, waktu, nilai) VALUES
    -- Budi: funnel penuh di hari pertama, lalu beli ulang tiga kali.
    ( 1, 1, 'page_view',  TIMESTAMP '2025-11-03 08:00:00', 0),
    ( 2, 1, 'add_to_cart', TIMESTAMP '2025-11-03 08:05:00', 0),
    ( 3, 1, 'checkout',    TIMESTAMP '2025-11-03 08:09:00', 0),
    ( 4, 1, 'purchase',    TIMESTAMP '2025-11-03 08:12:00', 150000),
    ( 5, 1, 'page_view',  TIMESTAMP '2025-12-18 20:00:00', 0),
    ( 6, 1, 'purchase',    TIMESTAMP '2025-12-18 20:05:00', 200000),
    ( 7, 1, 'page_view',  TIMESTAMP '2026-01-15 21:00:00', 0),
    ( 8, 1, 'purchase',    TIMESTAMP '2026-01-15 21:06:00', 175000),
    ( 9, 1, 'page_view',  TIMESTAMP '2026-02-10 09:00:00', 0),
    (10, 1, 'page_view',  TIMESTAMP '2026-02-10 09:06:00', 0),
    (11, 1, 'add_to_cart', TIMESTAMP '2026-02-10 09:11:00', 0),
    (12, 1, 'checkout',    TIMESTAMP '2026-02-10 09:14:00', 0),
    (13, 1, 'purchase',    TIMESTAMP '2026-02-10 09:16:00', 250000),
    (14, 1, 'page_view',  TIMESTAMP '2026-02-10 11:30:00', 0),
    (15, 1, 'page_view',  TIMESTAMP '2026-02-10 11:33:00', 0),
    (16, 1, 'add_to_cart', TIMESTAMP '2026-02-10 11:38:00', 0),
    (17, 1, 'page_view',  TIMESTAMP '2026-02-10 20:00:00', 0),
    (18, 1, 'page_view',  TIMESTAMP '2026-02-10 20:04:00', 0),
    -- Siti: berhenti di checkout saat mendaftar, beli di lain hari.
    (19, 2, 'page_view',  TIMESTAMP '2025-11-10 10:00:00', 0),
    (20, 2, 'add_to_cart', TIMESTAMP '2025-11-10 10:06:00', 0),
    (21, 2, 'checkout',    TIMESTAMP '2025-11-10 10:10:00', 0),
    (22, 2, 'page_view',  TIMESTAMP '2025-11-25 14:00:00', 0),
    (23, 2, 'purchase',    TIMESTAMP '2025-11-25 14:05:00', 100000),
    (24, 2, 'page_view',  TIMESTAMP '2025-12-12 09:00:00', 0),
    (25, 2, 'page_view',  TIMESTAMP '2026-01-05 09:30:00', 0),
    (26, 2, 'purchase',    TIMESTAMP '2026-01-05 09:36:00', 300000),
    -- Andi: berhenti di checkout, tidak pernah membeli.
    (27, 3, 'page_view',  TIMESTAMP '2025-12-01 11:00:00', 0),
    (28, 3, 'add_to_cart', TIMESTAMP '2025-12-01 11:05:00', 0),
    (29, 3, 'checkout',    TIMESTAMP '2025-12-01 11:09:00', 0),
    (30, 3, 'page_view',  TIMESTAMP '2026-01-20 16:00:00', 0),
    -- Rina: funnel penuh, lalu beli ulang dan kembali berkeliaran.
    (31, 4, 'page_view',  TIMESTAMP '2025-12-15 13:00:00', 0),
    (32, 4, 'add_to_cart', TIMESTAMP '2025-12-15 13:04:00', 0),
    (33, 4, 'checkout',    TIMESTAMP '2025-12-15 13:08:00', 0),
    (34, 4, 'purchase',    TIMESTAMP '2025-12-15 13:12:00', 120000),
    (35, 4, 'page_view',  TIMESTAMP '2026-01-22 20:00:00', 0),
    (36, 4, 'purchase',    TIMESTAMP '2026-01-22 20:06:00', 140000),
    (37, 4, 'page_view',  TIMESTAMP '2026-02-03 10:00:00', 0),
    -- Tono: hanya melihat-lihat.
    (38, 5, 'page_view',  TIMESTAMP '2026-01-05 08:00:00', 0),
    -- Wati: funnel penuh dan beli ulang.
    (39, 6, 'page_view',  TIMESTAMP '2026-01-20 15:00:00', 0),
    (40, 6, 'add_to_cart', TIMESTAMP '2026-01-20 15:05:00', 0),
    (41, 6, 'checkout',    TIMESTAMP '2026-01-20 15:09:00', 0),
    (42, 6, 'purchase',    TIMESTAMP '2026-01-20 15:13:00', 60000),
    (43, 6, 'page_view',  TIMESTAMP '2026-02-14 19:00:00', 0),
    (44, 6, 'purchase',    TIMESTAMP '2026-02-14 19:08:00', 80000),
    -- Joko: berhenti di keranjang.
    (45, 7, 'page_view',  TIMESTAMP '2026-02-02 09:00:00', 0),
    (46, 7, 'add_to_cart', TIMESTAMP '2026-02-02 09:07:00', 0),
    -- Mira: hanya sekali datang.
    (47, 8, 'page_view',  TIMESTAMP '2026-02-10 17:00:00', 0),
    -- Dewi: funnel penuh, aktif di tiga bulan berbeda.
    (48, 9, 'page_view',  TIMESTAMP '2025-11-28 10:00:00', 0),
    (49, 9, 'add_to_cart', TIMESTAMP '2025-11-28 10:04:00', 0),
    (50, 9, 'checkout',    TIMESTAMP '2025-11-28 10:08:00', 0),
    (51, 9, 'purchase',    TIMESTAMP '2025-11-28 10:11:00', 70000),
    (52, 9, 'page_view',  TIMESTAMP '2025-12-28 11:00:00', 0),
    (53, 9, 'purchase',    TIMESTAMP '2025-12-28 11:05:00', 55000),
    (54, 9, 'page_view',  TIMESTAMP '2026-02-01 09:00:00', 0),
    (55, 9, 'purchase',    TIMESTAMP '2026-02-01 09:06:00', 95000),
    -- Fajar: pembeli paling rajin, lima kali beli.
    (56, 10, 'page_view',  TIMESTAMP '2025-12-08 14:00:00', 0),
    (57, 10, 'add_to_cart', TIMESTAMP '2025-12-08 14:05:00', 0),
    (58, 10, 'checkout',    TIMESTAMP '2025-12-08 14:09:00', 0),
    (59, 10, 'purchase',    TIMESTAMP '2025-12-08 14:12:00', 35000),
    (60, 10, 'page_view',  TIMESTAMP '2025-12-30 18:00:00', 0),
    (61, 10, 'purchase',    TIMESTAMP '2025-12-30 18:05:00', 50000),
    (62, 10, 'page_view',  TIMESTAMP '2026-01-18 19:00:00', 0),
    (63, 10, 'purchase',    TIMESTAMP '2026-01-18 19:06:00', 65000),
    (64, 10, 'page_view',  TIMESTAMP '2026-02-06 20:00:00', 0),
    (65, 10, 'purchase',    TIMESTAMP '2026-02-06 20:05:00', 120000),
    (66, 10, 'page_view',  TIMESTAMP '2026-02-20 21:00:00', 0),
    (67, 10, 'purchase',    TIMESTAMP '2026-02-20 21:04:00', 150000);

SELECT jenis,
       count(*) AS jumlah_event,
       count(DISTINCT pengguna_id) AS pengguna_unik
FROM event_web
GROUP BY jenis
ORDER BY jenis;

Output yang Dihasilkan:

text
CREATE TABLE
CREATE TABLE
INSERT 0 10
INSERT 0 67
    jenis    | jumlah_event | pengguna_unik 
-------------+--------------+---------------
 add_to_cart |           10 |             8
 checkout    |            8 |             7
 page_view   |           31 |            10
 purchase    |           18 |             6
(4 rows)

Funnel Analysis: Konversi Tahap demi Tahap #

Funnel membandingkan jumlah pengguna yang mencapai tiap tahap. Dua cara menyusunnya: rantai self-join empat lapis (event tahap 1 di-join event tahap 2 milik pengguna sama, dan seterusnya), atau agregasi kondisional satu lapis — tiap pengguna diberi bendera per tahap lewat count(*) FILTER (WHERE jenis = ...) > 0, lalu bendera-bendera itu dihitung. Versi agregasi lebih pendek dan tidak melipatgandakan baris event; versi self-join unggul saat urutan tahap harus dipatuhi dalam satu kunjungan (kita bahas di akhir bagian):

sql
funnel.sql
-- Funnel analysis: berapa pengguna yang maju dari satu tahap ke tahap
-- berikutnya. Agregasi kondisional (count FILTER) per pengguna lebih
-- ringkas daripada rantai self-join empat lapis.

-- Langkah 1: tiap pengguna diberi bendera "sudah pernah mencapai tahap X".
-- LEFT JOIN menjaga pengguna tanpa event tetap terhitung (semua bendera f).
SELECT p.id,
       p.nama,
       count(*) FILTER (WHERE e.jenis = 'page_view') > 0 AS melihat,
       count(*) FILTER (WHERE e.jenis = 'add_to_cart') > 0 AS memasukkan,
       count(*) FILTER (WHERE e.jenis = 'checkout') > 0 AS mulai_bayar,
       count(*) FILTER (WHERE e.jenis = 'purchase') > 0 AS membeli
FROM pengguna p
LEFT JOIN event_web e ON e.pengguna_id = p.id
GROUP BY p.id, p.nama
ORDER BY p.id;

-- Langkah 2: hitung pengguna per tahap, lalu konversi antar-tahap dan
-- konversi total dihitung window function LAG / FIRST_VALUE di atas
-- hasil agregasi empat baris tersebut.
WITH per_pengguna AS (
    SELECT p.id,
           count(*) FILTER (WHERE e.jenis = 'page_view') > 0 AS melihat,
           count(*) FILTER (WHERE e.jenis = 'add_to_cart') > 0 AS memasukkan,
           count(*) FILTER (WHERE e.jenis = 'checkout') > 0 AS mulai_bayar,
           count(*) FILTER (WHERE e.jenis = 'purchase') > 0 AS membeli
    FROM pengguna p
    LEFT JOIN event_web e ON e.pengguna_id = p.id
    GROUP BY p.id
),
tahap AS (
    SELECT 1 AS urutan, 'page_view'  AS tahap, count(*) FILTER (WHERE melihat) AS pengguna FROM per_pengguna
    UNION ALL
    SELECT 2, 'add_to_cart', count(*) FILTER (WHERE memasukkan) FROM per_pengguna
    UNION ALL
    SELECT 3, 'checkout',    count(*) FILTER (WHERE mulai_bayar) FROM per_pengguna
    UNION ALL
    SELECT 4, 'purchase',    count(*) FILTER (WHERE membeli) FROM per_pengguna
)
SELECT urutan,
       tahap,
       pengguna,
       round(100.0 * pengguna / lag(pengguna) OVER (ORDER BY urutan), 1) AS konversi_langkah_pct,
       round(100.0 * pengguna / first_value(pengguna) OVER (ORDER BY urutan), 1) AS konversi_total_pct
FROM tahap
ORDER BY urutan;

Output yang Dihasilkan:

text
 id | nama  | melihat | memasukkan | mulai_bayar | membeli 
----+-------+---------+------------+-------------+---------
  1 | Budi  | t       | t          | t           | t
  2 | Siti  | t       | t          | t           | t
  3 | Andi  | t       | t          | t           | f
  4 | Rina  | t       | t          | t           | t
  5 | Tono  | t       | f          | f           | f
  6 | Wati  | t       | t          | t           | t
  7 | Joko  | t       | t          | f           | f
  8 | Mira  | t       | f          | f           | f
  9 | Dewi  | t       | t          | t           | t
 10 | Fajar | t       | t          | t           | t
(10 rows)

 urutan |    tahap    | pengguna | konversi_langkah_pct | konversi_total_pct 
--------+-------------+----------+----------------------+--------------------
      1 | page_view   |       10 |                      |              100.0
      2 | add_to_cart |        8 |                 80.0 |               80.0
      3 | checkout    |        7 |                 87.5 |               70.0
      4 | purchase    |        6 |                 85.7 |               60.0
(4 rows)

Tabel antara memperlihatkan bendera per pengguna: sepuluh baris, dengan Andi berhenti di mulai_bayar, Joko di memasukkan, Tono dan Mira hanya melihat. Hasil akhirnya dibaca seperti laporan mingguan: 10 pengunjung menyusut menjadi 8 (konversi 80%), 7 (87,5% dari tahap sebelumnya), dan 6 pembeli (85,7%) — dari pintu masuk sampai kasir tersisa 60%. Tahap add_to_cart adalah penyaring terbesar; itulah tempat percobaan A/B paling layak ditempatkan.

Cohort Retention: Grid Bulan Daftar × Bulan Aktif #

Kohort mengelompokkan pengguna menurut bulan pendaftaran, lalu menanyakan: pada bulan-bulan berikutnya, berapa persen yang masih beraktivitas? Struktur hasilnya grid — baris kohort, kolom offset bulan — dan grid seperti itu dibangun agregasi FILTER tanpa extension apa pun. Offset bulan dihitung dari selisih tahun dan bulan, karena pengurangan dua date menghasilkan jumlah hari, bukan jumlah bulan:

sql
kohort.sql
-- Cohort retention: pengguna dikelompokkan menurut bulan mendaftar
-- (kohort), lalu dihitung persentase yang masih aktif pada bulan-bulan
-- berikutnya (retensi). Grid kohort x bulan dibangun agregasi FILTER --
-- pivot crosstab-style tanpa extension.

-- Langkah 1: pasangan (pengguna, bulan aktif) -- DISTINCT membuang
-- duplikat event dalam bulan yang sama; offset bulan dihitung dari
-- selisih tahun dan bulan terhadap tanggal daftar.
SELECT DISTINCT
       p.id,
       p.nama,
       date_trunc('month', p.tanggal_daftar)::date AS kohort,
       ((date_part('year', e.waktu) - date_part('year', p.tanggal_daftar)) * 12
        + (date_part('month', e.waktu) - date_part('month', p.tanggal_daftar)))::integer AS bulan_ke
FROM pengguna p
JOIN event_web e ON e.pengguna_id = p.id
ORDER BY p.id, bulan_ke;

-- Langkah 2: grid retensi -- baris per kohort, kolom per offset bulan.
-- bulan_ke = 0 adalah bulan pendaftaran (baseline kohort). Sel yang
-- bulannya belum teramati (melewati akhir data, Februari 2026) dibiarkan
-- NULL lewat CASE -- bukan 0, karena 0 berarti churn yang terbukti.
WITH aktif AS (
    SELECT DISTINCT
           p.id,
           date_trunc('month', p.tanggal_daftar)::date AS kohort,
           ((date_part('year', e.waktu) - date_part('year', p.tanggal_daftar)) * 12
            + (date_part('month', e.waktu) - date_part('month', p.tanggal_daftar)))::integer AS bulan_ke
    FROM pengguna p
    JOIN event_web e ON e.pengguna_id = p.id
),
grid AS (
    SELECT kohort,
           count(*) FILTER (WHERE bulan_ke = 0) AS pengguna,
           count(*) FILTER (WHERE bulan_ke = 1) AS bulan_1,
           count(*) FILTER (WHERE bulan_ke = 2) AS bulan_2,
           count(*) FILTER (WHERE bulan_ke = 3) AS bulan_3
    FROM aktif
    GROUP BY kohort
)
SELECT to_char(kohort, 'YYYY-MM') AS bulan_daftar,
       pengguna,
       CASE WHEN kohort + INTERVAL '1 month' > DATE '2026-02-01' THEN NULL
            ELSE round(100.0 * bulan_1 / pengguna)::integer END AS retensi_m1_pct,
       CASE WHEN kohort + INTERVAL '2 month' > DATE '2026-02-01' THEN NULL
            ELSE round(100.0 * bulan_2 / pengguna)::integer END AS retensi_m2_pct,
       CASE WHEN kohort + INTERVAL '3 month' > DATE '2026-02-01' THEN NULL
            ELSE round(100.0 * bulan_3 / pengguna)::integer END AS retensi_m3_pct
FROM grid
ORDER BY kohort;

Output yang Dihasilkan:

text
 id | nama  |   kohort   | bulan_ke 
----+-------+------------+----------
  1 | Budi  | 2025-11-01 |        0
  1 | Budi  | 2025-11-01 |        1
  1 | Budi  | 2025-11-01 |        2
  1 | Budi  | 2025-11-01 |        3
  2 | Siti  | 2025-11-01 |        0
  2 | Siti  | 2025-11-01 |        1
  2 | Siti  | 2025-11-01 |        2
  3 | Andi  | 2025-12-01 |        0
  3 | Andi  | 2025-12-01 |        1
  4 | Rina  | 2025-12-01 |        0
  4 | Rina  | 2025-12-01 |        1
  4 | Rina  | 2025-12-01 |        2
  5 | Tono  | 2026-01-01 |        0
  6 | Wati  | 2026-01-01 |        0
  6 | Wati  | 2026-01-01 |        1
  7 | Joko  | 2026-02-01 |        0
  8 | Mira  | 2026-02-01 |        0
  9 | Dewi  | 2025-11-01 |        0
  9 | Dewi  | 2025-11-01 |        1
  9 | Dewi  | 2025-11-01 |        3
 10 | Fajar | 2025-12-01 |        0
 10 | Fajar | 2025-12-01 |        1
 10 | Fajar | 2025-12-01 |        2
(23 rows)

 bulan_daftar | pengguna | retensi_m1_pct | retensi_m2_pct | retensi_m3_pct 
--------------+----------+----------------+----------------+----------------
 2025-11      |        3 |            100 |             67 |             67
 2025-12      |        3 |            100 |             67 |               
 2026-01      |        2 |             50 |                |               
 2026-02      |        2 |                |                |               
(4 rows)

Baca gridnya seperti papan catur yang menyempit ke kanan: kohort November (Budi, Siti, Dewi) bertahan penuh di bulan pertama (100%), turun ke 67% di bulan kedua, dan 67% lagi di bulan ketiga — Budi dan Dewi masih belanja pada Februari. Kohort Januari hanya menyisakan Wati di bulan kedua (50%), dan kohort Februari baru bisa dinilai bulan depan — sel-selnya sengaja NULL, bukan 0. Bandingkan menyamping (retensi antar kohort pada offset sama) untuk menilai kualitas pendaftaran tiap periode; bandingkan menurun (satu kohort menua) untuk menilai produknya.

Segmentasi RFM: ntile dan Label CASE #

RFM memberi tiga angka per pelanggan — Recency (berapa hari sejak terakhir beli), Frequency (berapa kali beli), Monetary (total belanja) — lalu memotong tiap angka menjadi peringkat. ntile(5) adalah alat pemotongnya: mengurutkan baris menurut satu metrik lalu membagi menjadi lima ember semerata mungkin. Tiga skor digabung menjadi kode, dan CASE menerjemahkan kombinasi skor menjadi label yang bisa langsung diserahkan tim marketing:

sql
rfm.sql
-- RFM segmentation: Recency (berapa hari sejak terakhir beli), Frequency
-- (berapa kali beli), Monetary (total nilai beli). Ketiganya dipotong
-- menjadi lima ember ntile(5), digabung jadi kode, lalu diberi label
-- segmen lewat CASE. Tanggal analisis dibekukan 2026-02-28 agar hasil
-- reproducible -- di produksi ganti dengan current_date.

-- Langkah 1: metrik dasar per pembeli (pengguna tanpa pembelian disaring).
SELECT p.id,
       p.nama,
       (DATE '2026-02-28' - max(e.waktu) FILTER (WHERE e.jenis = 'purchase')::date) AS recency_hari,
       count(*) FILTER (WHERE e.jenis = 'purchase') AS frequency,
       sum(e.nilai) FILTER (WHERE e.jenis = 'purchase') AS monetary
FROM pengguna p
JOIN event_web e ON e.pengguna_id = p.id
GROUP BY p.id, p.nama
HAVING count(*) FILTER (WHERE e.jenis = 'purchase') > 0
ORDER BY p.id;

-- Langkah 2: skor ntile + kode + label. Skor R tinggi = baru saja beli
-- (urut recency menurun); skor F dan M tinggi = sering dan besar
-- (urut menaik; id sebagai tie-breaker agar deterministik).
WITH pembeli AS (
    SELECT p.id,
           p.nama,
           (DATE '2026-02-28' - max(e.waktu) FILTER (WHERE e.jenis = 'purchase')::date) AS recency_hari,
           count(*) FILTER (WHERE e.jenis = 'purchase') AS frequency,
           sum(e.nilai) FILTER (WHERE e.jenis = 'purchase') AS monetary
    FROM pengguna p
    JOIN event_web e ON e.pengguna_id = p.id
    GROUP BY p.id, p.nama
    HAVING count(*) FILTER (WHERE e.jenis = 'purchase') > 0
),
ber_skor AS (
    SELECT id,
           nama,
           recency_hari,
           frequency,
           monetary,
           ntile(5) OVER (ORDER BY recency_hari DESC) AS skor_r,
           ntile(5) OVER (ORDER BY frequency ASC, id ASC) AS skor_f,
           ntile(5) OVER (ORDER BY monetary ASC, id ASC) AS skor_m
    FROM pembeli
)
SELECT nama,
       recency_hari,
       frequency,
       monetary,
       skor_r || '-' || skor_f || '-' || skor_m AS kode_rfm,
       CASE
           WHEN skor_r >= 3 AND skor_f >= 3 AND skor_m >= 3 THEN 'Champion'
           WHEN skor_f >= 3 AND skor_m >= 3 THEN 'Pelanggan Setia'
           WHEN skor_r >= 3 AND skor_f <= 2 THEN 'Berpotensi'
           WHEN skor_r <= 2 AND skor_f >= 2 THEN 'Berisiko'
           WHEN skor_r <= 1 THEN 'Nyaris Hilang'
           ELSE 'Perlu Ditinjau'
       END AS segmen
FROM ber_skor
ORDER BY skor_r DESC, skor_f DESC, skor_m DESC, id;

Output yang Dihasilkan:

text
 id | nama  | recency_hari | frequency | monetary  
----+-------+--------------+-----------+-----------
  1 | Budi  |           18 |         4 | 775000.00
  2 | Siti  |           54 |         2 | 400000.00
  4 | Rina  |           37 |         2 | 260000.00
  6 | Wati  |           14 |         2 | 140000.00
  9 | Dewi  |           27 |         3 | 220000.00
 10 | Fajar |            8 |         5 | 420000.00
(6 rows)

 nama  | recency_hari | frequency | monetary  | kode_rfm |    segmen     
-------+--------------+-----------+-----------+----------+---------------
 Fajar |            8 |         5 | 420000.00 | 5-5-4    | Champion
 Wati  |           14 |         2 | 140000.00 | 4-2-1    | Berpotensi
 Budi  |           18 |         4 | 775000.00 | 3-4-5    | Champion
 Dewi  |           27 |         3 | 220000.00 | 2-3-1    | Berisiko
 Siti  |           54 |         2 | 400000.00 | 1-1-3    | Nyaris Hilang
 Rina  |           37 |         2 | 260000.00 | 1-1-2    | Nyaris Hilang
(6 rows)

Mari kita bedah hasilnya:

Langkah 1 menghasilkan enam pembeli; HAVING ... FILTER menyaring Andi, Joko, Tono, dan Mira yang tidak pernah membeli — menghitung RFM atas non-pembeli hanya menghasilkan angka kosong.
ntile(5) pada enam baris membagi ember bermula [2,1,1,1,1] — dua baris terbawah berbagi ember 1, sisanya naik satu-satu. Pada data nyata dengan ratusan pelanggan kelima ember terisi penuh; pola query-nya tidak berubah.
Arah urutan menentukan makna skor: skor R diurutkan recency_hari DESC sehingga pelanggan paling lama tidak belanja mendapat 1 dan yang baru beli mendapat 5; skor F dan M diurutkan menaik sehingga yang terbaik tetap di ember 5. id ASC sebagai tie-breaker menjaga hasil tetap sama setiap kali dijalankan.
Label akhirnya membaca kombinasi: Fajar (5-5-4, beli lima kali, terakhir 8 hari lalu) dan Budi (3-4-5, nilai belanja terbesar) menjadi Champion; Wati baru-baru ini aktif tetapi masih kecil (4-2-1) sehingga berlabel Berpotensi; Siti dan Rina yang sudah lebih sebulan tidak belanja jatuh ke Nyaris Hilang.

Sessionization: Sesi dari Jeda 30 Menit #

Event mentah per pengguna tidak mengatakan “berapa kali berkunjung” — seorang pengguna bisa menghasilkan puluhan page_view dalam satu duduk. Sessionization memotong deret event menjadi sesi: batas sesi adalah jeda lebih dari 30 menit (1.800 detik) dari event sebelumnya. Polanya persis deteksi pulau di chapter Gap and Island Problem : LAG membawa event sebelumnya, bendera batas dibangkitkan dari selisih waktu, dan jumlah berjalan SUM atas bendera menjadi nomor sesi:

sql
sesi.sql
-- Sessionization: kelompokkan event satu pengguna menjadi sesi kunjungan.
-- Batas sesi = jeda ke event sebelumnya lebih dari 30 menit (1800 detik).
-- Nomor sesi = jumlah berjalan (running SUM) atas bendera batas -- pola
-- LAG + SUM yang sama dengan deteksi pulau di chapter sebelumnya,
-- hanya medianya interval waktu, bukan tanggal.

-- Langkah 1: bacaan Budi 10 Februari 2026 -- jeda menit dan bendera batas
-- terlihat telanjang sebelum diagregasi. Tiga sesi terbentuk: pagi (rapat
-- belanja sampai purchase), siang (kembali berkeliling), dan malam.
WITH ber_jarak AS (
    SELECT waktu,
           jenis,
           lag(waktu) OVER (ORDER BY waktu) AS sebelumnya
    FROM event_web
    WHERE pengguna_id = 1
      AND waktu >= TIMESTAMP '2026-02-10 00:00:00'
      AND waktu <  TIMESTAMP '2026-02-11 00:00:00'
)
SELECT waktu,
       jenis,
       sebelumnya,
       round(extract(epoch FROM (waktu - sebelumnya)) / 60)::integer AS jeda_menit,
       CASE
           WHEN sebelumnya IS NULL THEN 1
           WHEN extract(epoch FROM (waktu - sebelumnya)) > 1800 THEN 1
           ELSE 0
       END AS batas_sesi
FROM ber_jarak
ORDER BY waktu;

-- Langkah 2: statistik per sesi untuk seluruh riwayat Budi -- nomor sesi
-- dari running SUM, lalu agregasi per sesi: jumlah event, rentang jam,
-- pembelian, dan nilai belanjanya.
WITH bertanda AS (
    SELECT pengguna_id,
           waktu,
           jenis,
           nilai,
           CASE
               WHEN lag(waktu) OVER w IS NULL THEN 1
               WHEN extract(epoch FROM (waktu - lag(waktu) OVER w)) > 1800 THEN 1
               ELSE 0
           END AS batas_sesi
    FROM event_web
    WHERE pengguna_id = 1
    WINDOW w AS (PARTITION BY pengguna_id ORDER BY waktu)
),
ber_sesi AS (
    SELECT *,
           sum(batas_sesi) OVER (PARTITION BY pengguna_id ORDER BY waktu) AS sesi_ke
    FROM bertanda
)
SELECT sesi_ke,
       date_trunc('day', min(waktu))::date AS tanggal,
       count(*) AS jumlah_event,
       to_char(min(waktu), 'HH24:MI') AS mulai,
       to_char(max(waktu), 'HH24:MI') AS selesai,
       count(*) FILTER (WHERE jenis = 'purchase') AS pembelian,
       coalesce(sum(nilai) FILTER (WHERE jenis = 'purchase'), 0) AS nilai_beli
FROM ber_sesi
GROUP BY sesi_ke
ORDER BY sesi_ke;

Output yang Dihasilkan:

text
        waktu        |    jenis    |     sebelumnya      | jeda_menit | batas_sesi 
---------------------+-------------+---------------------+------------+------------
 2026-02-10 09:00:00 | page_view   |                     |            |          1
 2026-02-10 09:06:00 | page_view   | 2026-02-10 09:00:00 |          6 |          0
 2026-02-10 09:11:00 | add_to_cart | 2026-02-10 09:06:00 |          5 |          0
 2026-02-10 09:14:00 | checkout    | 2026-02-10 09:11:00 |          3 |          0
 2026-02-10 09:16:00 | purchase    | 2026-02-10 09:14:00 |          2 |          0
 2026-02-10 11:30:00 | page_view   | 2026-02-10 09:16:00 |        134 |          1
 2026-02-10 11:33:00 | page_view   | 2026-02-10 11:30:00 |          3 |          0
 2026-02-10 11:38:00 | add_to_cart | 2026-02-10 11:33:00 |          5 |          0
 2026-02-10 20:00:00 | page_view   | 2026-02-10 11:38:00 |        502 |          1
 2026-02-10 20:04:00 | page_view   | 2026-02-10 20:00:00 |          4 |          0
(10 rows)

 sesi_ke |  tanggal   | jumlah_event | mulai | selesai | pembelian | nilai_beli 
---------+------------+--------------+-------+---------+-----------+------------
       1 | 2025-11-03 |            4 | 08:00 | 08:12   |         1 |  150000.00
       2 | 2025-12-18 |            2 | 20:00 | 20:05   |         1 |  200000.00
       3 | 2026-01-15 |            2 | 21:00 | 21:06   |         1 |  175000.00
       4 | 2026-02-10 |            5 | 09:00 | 09:16   |         1 |  250000.00
       5 | 2026-02-10 |            3 | 11:30 | 11:38   |         0 |          0
       6 | 2026-02-10 |            2 | 20:00 | 20:04   |         0 |          0
(6 rows)

Tabel antara 10 Februari memperlihatkan mekanismenya mentah-mentah: jeda 6, 5, 3, 2 menit di sesi pagi tidak menyala, jeda 134 menit ke jam 11:30 menyalakan bendera, dan jeda 502 menit ke malam hari menyalakannya lagi. Hasil akhir Budi berbunyi: enam sesi sejak November, tiga di antaranya pada 10 Februari; hanya sesi pertama tiap hari yang membawa pembelian; sesi siang dan malam 10 Februari murni berkeliling. Ganti WHERE pengguna_id = 1 menjadi tanpa filter, dan PARTITION BY pengguna_id yang sudah ada membuat pola yang sama bekerja untuk semua pengguna sekaligus.

Empat pola ini menuntaskan pertanyaan analitik yang bentuknya tetap. Pertanyaan berikutnya berubah-ubah bentuknya sendiri: nama tabel berasal dari konfigurasi, filter pencarian datang dari form pengguna, dan query harus dibangun saat runtime — wilayah Dynamic SQL, chapter berikutnya.

Referensi #

Aggregate Functions — FILTER, ntile, dan percentile_cont sebagai fondasi agregasi analitik.
Window Functions — LAG, FIRST_VALUE, dan ROW_NUMBER untuk perhitungan lintas-baris.
WITH Queries (Common Table Expressions) — merangkai tahap pipeline analitik menjadi CTE bernama.

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- Tabel latihan chapter Analytical Query: pengguna dan log event web
-- Toko Maju Jaya, November 2025 sampai Februari 2026 (tanggal tetap).
-- Satu baris event_web = satu aksi; jenis bertingkat funnel:
-- page_view -> add_to_cart -> checkout -> purchase.
CREATE TABLE pengguna (
    id            integer PRIMARY KEY,
    nama          text NOT NULL,
    email         text NOT NULL,
    tanggal_daftar date NOT NULL
);

CREATE TABLE event_web (
    id           integer PRIMARY KEY,
    pengguna_id  integer NOT NULL REFERENCES pengguna (id),
    jenis        text NOT NULL,
    waktu        timestamp NOT NULL,
    nilai        numeric(12,2) NOT NULL
);

INSERT INTO pengguna (id, nama, email, tanggal_daftar) VALUES
    ( 1, 'Budi',  '[email protected]',  DATE '2025-11-03'),
    ( 2, 'Siti',  '[email protected]',  DATE '2025-11-10'),
    ( 3, 'Andi',  '[email protected]',  DATE '2025-12-01'),
    ( 4, 'Rina',  '[email protected]',  DATE '2025-12-15'),
    ( 5, 'Tono',  '[email protected]',  DATE '2026-01-05'),
    ( 6, 'Wati',  '[email protected]',  DATE '2026-01-20'),
    ( 7, 'Joko',  '[email protected]',  DATE '2026-02-02'),
    ( 8, 'Mira',  '[email protected]',  DATE '2026-02-10'),
    ( 9, 'Dewi',  '[email protected]',  DATE '2025-11-28'),
    (10, 'Fajar', '[email protected]', DATE '2025-12-08');

INSERT INTO event_web (id, pengguna_id, jenis, waktu, nilai) VALUES
    -- Budi: funnel penuh di hari pertama, lalu beli ulang tiga kali.
    ( 1, 1, 'page_view',  TIMESTAMP '2025-11-03 08:00:00', 0),
    ( 2, 1, 'add_to_cart', TIMESTAMP '2025-11-03 08:05:00', 0),
    ( 3, 1, 'checkout',    TIMESTAMP '2025-11-03 08:09:00', 0),
    ( 4, 1, 'purchase',    TIMESTAMP '2025-11-03 08:12:00', 150000),
    ( 5, 1, 'page_view',  TIMESTAMP '2025-12-18 20:00:00', 0),
    ( 6, 1, 'purchase',    TIMESTAMP '2025-12-18 20:05:00', 200000),
    ( 7, 1, 'page_view',  TIMESTAMP '2026-01-15 21:00:00', 0),
    ( 8, 1, 'purchase',    TIMESTAMP '2026-01-15 21:06:00', 175000),
    ( 9, 1, 'page_view',  TIMESTAMP '2026-02-10 09:00:00', 0),
    (10, 1, 'page_view',  TIMESTAMP '2026-02-10 09:06:00', 0),
    (11, 1, 'add_to_cart', TIMESTAMP '2026-02-10 09:11:00', 0),
    (12, 1, 'checkout',    TIMESTAMP '2026-02-10 09:14:00', 0),
    (13, 1, 'purchase',    TIMESTAMP '2026-02-10 09:16:00', 250000),
    (14, 1, 'page_view',  TIMESTAMP '2026-02-10 11:30:00', 0),
    (15, 1, 'page_view',  TIMESTAMP '2026-02-10 11:33:00', 0),
    (16, 1, 'add_to_cart', TIMESTAMP '2026-02-10 11:38:00', 0),
    (17, 1, 'page_view',  TIMESTAMP '2026-02-10 20:00:00', 0),
    (18, 1, 'page_view',  TIMESTAMP '2026-02-10 20:04:00', 0),
    -- Siti: berhenti di checkout saat mendaftar, beli di lain hari.
    (19, 2, 'page_view',  TIMESTAMP '2025-11-10 10:00:00', 0),
    (20, 2, 'add_to_cart', TIMESTAMP '2025-11-10 10:06:00', 0),
    (21, 2, 'checkout',    TIMESTAMP '2025-11-10 10:10:00', 0),
    (22, 2, 'page_view',  TIMESTAMP '2025-11-25 14:00:00', 0),
    (23, 2, 'purchase',    TIMESTAMP '2025-11-25 14:05:00', 100000),
    (24, 2, 'page_view',  TIMESTAMP '2025-12-12 09:00:00', 0),
    (25, 2, 'page_view',  TIMESTAMP '2026-01-05 09:30:00', 0),
    (26, 2, 'purchase',    TIMESTAMP '2026-01-05 09:36:00', 300000),
    -- Andi: berhenti di checkout, tidak pernah membeli.
    (27, 3, 'page_view',  TIMESTAMP '2025-12-01 11:00:00', 0),
    (28, 3, 'add_to_cart', TIMESTAMP '2025-12-01 11:05:00', 0),
    (29, 3, 'checkout',    TIMESTAMP '2025-12-01 11:09:00', 0),
    (30, 3, 'page_view',  TIMESTAMP '2026-01-20 16:00:00', 0),
    -- Rina: funnel penuh, lalu beli ulang dan kembali berkeliaran.
    (31, 4, 'page_view',  TIMESTAMP '2025-12-15 13:00:00', 0),
    (32, 4, 'add_to_cart', TIMESTAMP '2025-12-15 13:04:00', 0),
    (33, 4, 'checkout',    TIMESTAMP '2025-12-15 13:08:00', 0),
    (34, 4, 'purchase',    TIMESTAMP '2025-12-15 13:12:00', 120000),
    (35, 4, 'page_view',  TIMESTAMP '2026-01-22 20:00:00', 0),
    (36, 4, 'purchase',    TIMESTAMP '2026-01-22 20:06:00', 140000),
    (37, 4, 'page_view',  TIMESTAMP '2026-02-03 10:00:00', 0),
    -- Tono: hanya melihat-lihat.
    (38, 5, 'page_view',  TIMESTAMP '2026-01-05 08:00:00', 0),
    -- Wati: funnel penuh dan beli ulang.
    (39, 6, 'page_view',  TIMESTAMP '2026-01-20 15:00:00', 0),
    (40, 6, 'add_to_cart', TIMESTAMP '2026-01-20 15:05:00', 0),
    (41, 6, 'checkout',    TIMESTAMP '2026-01-20 15:09:00', 0),
    (42, 6, 'purchase',    TIMESTAMP '2026-01-20 15:13:00', 60000),
    (43, 6, 'page_view',  TIMESTAMP '2026-02-14 19:00:00', 0),
    (44, 6, 'purchase',    TIMESTAMP '2026-02-14 19:08:00', 80000),
    -- Joko: berhenti di keranjang.
    (45, 7, 'page_view',  TIMESTAMP '2026-02-02 09:00:00', 0),
    (46, 7, 'add_to_cart', TIMESTAMP '2026-02-02 09:07:00', 0),
    -- Mira: hanya sekali datang.
    (47, 8, 'page_view',  TIMESTAMP '2026-02-10 17:00:00', 0),
    -- Dewi: funnel penuh, aktif di tiga bulan berbeda.
    (48, 9, 'page_view',  TIMESTAMP '2025-11-28 10:00:00', 0),
    (49, 9, 'add_to_cart', TIMESTAMP '2025-11-28 10:04:00', 0),
    (50, 9, 'checkout',    TIMESTAMP '2025-11-28 10:08:00', 0),
    (51, 9, 'purchase',    TIMESTAMP '2025-11-28 10:11:00', 70000),
    (52, 9, 'page_view',  TIMESTAMP '2025-12-28 11:00:00', 0),
    (53, 9, 'purchase',    TIMESTAMP '2025-12-28 11:05:00', 55000),
    (54, 9, 'page_view',  TIMESTAMP '2026-02-01 09:00:00', 0),
    (55, 9, 'purchase',    TIMESTAMP '2026-02-01 09:06:00', 95000),
    -- Fajar: pembeli paling rajin, lima kali beli.
    (56, 10, 'page_view',  TIMESTAMP '2025-12-08 14:00:00', 0),
    (57, 10, 'add_to_cart', TIMESTAMP '2025-12-08 14:05:00', 0),
    (58, 10, 'checkout',    TIMESTAMP '2025-12-08 14:09:00', 0),
    (59, 10, 'purchase',    TIMESTAMP '2025-12-08 14:12:00', 35000),
    (60, 10, 'page_view',  TIMESTAMP '2025-12-30 18:00:00', 0),
    (61, 10, 'purchase',    TIMESTAMP '2025-12-30 18:05:00', 50000),
    (62, 10, 'page_view',  TIMESTAMP '2026-01-18 19:00:00', 0),
    (63, 10, 'purchase',    TIMESTAMP '2026-01-18 19:06:00', 65000),
    (64, 10, 'page_view',  TIMESTAMP '2026-02-06 20:00:00', 0),
    (65, 10, 'purchase',    TIMESTAMP '2026-02-06 20:05:00', 120000),
    (66, 10, 'page_view',  TIMESTAMP '2026-02-20 21:00:00', 0),
    (67, 10, 'purchase',    TIMESTAMP '2026-02-20 21:04:00', 150000);

SELECT jenis,
       count(*) AS jumlah_event,
       count(DISTINCT pengguna_id) AS pengguna_unik
FROM event_web
GROUP BY jenis
ORDER BY jenis;

sql
-- Funnel analysis: berapa pengguna yang maju dari satu tahap ke tahap
-- berikutnya. Agregasi kondisional (count FILTER) per pengguna lebih
-- ringkas daripada rantai self-join empat lapis.

-- Langkah 1: tiap pengguna diberi bendera "sudah pernah mencapai tahap X".
-- LEFT JOIN menjaga pengguna tanpa event tetap terhitung (semua bendera f).
SELECT p.id,
       p.nama,
       count(*) FILTER (WHERE e.jenis = 'page_view') > 0 AS melihat,
       count(*) FILTER (WHERE e.jenis = 'add_to_cart') > 0 AS memasukkan,
       count(*) FILTER (WHERE e.jenis = 'checkout') > 0 AS mulai_bayar,
       count(*) FILTER (WHERE e.jenis = 'purchase') > 0 AS membeli
FROM pengguna p
LEFT JOIN event_web e ON e.pengguna_id = p.id
GROUP BY p.id, p.nama
ORDER BY p.id;

-- Langkah 2: hitung pengguna per tahap, lalu konversi antar-tahap dan
-- konversi total dihitung window function LAG / FIRST_VALUE di atas
-- hasil agregasi empat baris tersebut.
WITH per_pengguna AS (
    SELECT p.id,
           count(*) FILTER (WHERE e.jenis = 'page_view') > 0 AS melihat,
           count(*) FILTER (WHERE e.jenis = 'add_to_cart') > 0 AS memasukkan,
           count(*) FILTER (WHERE e.jenis = 'checkout') > 0 AS mulai_bayar,
           count(*) FILTER (WHERE e.jenis = 'purchase') > 0 AS membeli
    FROM pengguna p
    LEFT JOIN event_web e ON e.pengguna_id = p.id
    GROUP BY p.id
),
tahap AS (
    SELECT 1 AS urutan, 'page_view'  AS tahap, count(*) FILTER (WHERE melihat) AS pengguna FROM per_pengguna
    UNION ALL
    SELECT 2, 'add_to_cart', count(*) FILTER (WHERE memasukkan) FROM per_pengguna
    UNION ALL
    SELECT 3, 'checkout',    count(*) FILTER (WHERE mulai_bayar) FROM per_pengguna
    UNION ALL
    SELECT 4, 'purchase',    count(*) FILTER (WHERE membeli) FROM per_pengguna
)
SELECT urutan,
       tahap,
       pengguna,
       round(100.0 * pengguna / lag(pengguna) OVER (ORDER BY urutan), 1) AS konversi_langkah_pct,
       round(100.0 * pengguna / first_value(pengguna) OVER (ORDER BY urutan), 1) AS konversi_total_pct
FROM tahap
ORDER BY urutan;

sql
-- Cohort retention: pengguna dikelompokkan menurut bulan mendaftar
-- (kohort), lalu dihitung persentase yang masih aktif pada bulan-bulan
-- berikutnya (retensi). Grid kohort x bulan dibangun agregasi FILTER --
-- pivot crosstab-style tanpa extension.

-- Langkah 1: pasangan (pengguna, bulan aktif) -- DISTINCT membuang
-- duplikat event dalam bulan yang sama; offset bulan dihitung dari
-- selisih tahun dan bulan terhadap tanggal daftar.
SELECT DISTINCT
       p.id,
       p.nama,
       date_trunc('month', p.tanggal_daftar)::date AS kohort,
       ((date_part('year', e.waktu) - date_part('year', p.tanggal_daftar)) * 12
        + (date_part('month', e.waktu) - date_part('month', p.tanggal_daftar)))::integer AS bulan_ke
FROM pengguna p
JOIN event_web e ON e.pengguna_id = p.id
ORDER BY p.id, bulan_ke;

-- Langkah 2: grid retensi -- baris per kohort, kolom per offset bulan.
-- bulan_ke = 0 adalah bulan pendaftaran (baseline kohort). Sel yang
-- bulannya belum teramati (melewati akhir data, Februari 2026) dibiarkan
-- NULL lewat CASE -- bukan 0, karena 0 berarti churn yang terbukti.
WITH aktif AS (
    SELECT DISTINCT
           p.id,
           date_trunc('month', p.tanggal_daftar)::date AS kohort,
           ((date_part('year', e.waktu) - date_part('year', p.tanggal_daftar)) * 12
            + (date_part('month', e.waktu) - date_part('month', p.tanggal_daftar)))::integer AS bulan_ke
    FROM pengguna p
    JOIN event_web e ON e.pengguna_id = p.id
),
grid AS (
    SELECT kohort,
           count(*) FILTER (WHERE bulan_ke = 0) AS pengguna,
           count(*) FILTER (WHERE bulan_ke = 1) AS bulan_1,
           count(*) FILTER (WHERE bulan_ke = 2) AS bulan_2,
           count(*) FILTER (WHERE bulan_ke = 3) AS bulan_3
    FROM aktif
    GROUP BY kohort
)
SELECT to_char(kohort, 'YYYY-MM') AS bulan_daftar,
       pengguna,
       CASE WHEN kohort + INTERVAL '1 month' > DATE '2026-02-01' THEN NULL
            ELSE round(100.0 * bulan_1 / pengguna)::integer END AS retensi_m1_pct,
       CASE WHEN kohort + INTERVAL '2 month' > DATE '2026-02-01' THEN NULL
            ELSE round(100.0 * bulan_2 / pengguna)::integer END AS retensi_m2_pct,
       CASE WHEN kohort + INTERVAL '3 month' > DATE '2026-02-01' THEN NULL
            ELSE round(100.0 * bulan_3 / pengguna)::integer END AS retensi_m3_pct
FROM grid
ORDER BY kohort;

sql
-- RFM segmentation: Recency (berapa hari sejak terakhir beli), Frequency
-- (berapa kali beli), Monetary (total nilai beli). Ketiganya dipotong
-- menjadi lima ember ntile(5), digabung jadi kode, lalu diberi label
-- segmen lewat CASE. Tanggal analisis dibekukan 2026-02-28 agar hasil
-- reproducible -- di produksi ganti dengan current_date.

-- Langkah 1: metrik dasar per pembeli (pengguna tanpa pembelian disaring).
SELECT p.id,
       p.nama,
       (DATE '2026-02-28' - max(e.waktu) FILTER (WHERE e.jenis = 'purchase')::date) AS recency_hari,
       count(*) FILTER (WHERE e.jenis = 'purchase') AS frequency,
       sum(e.nilai) FILTER (WHERE e.jenis = 'purchase') AS monetary
FROM pengguna p
JOIN event_web e ON e.pengguna_id = p.id
GROUP BY p.id, p.nama
HAVING count(*) FILTER (WHERE e.jenis = 'purchase') > 0
ORDER BY p.id;

-- Langkah 2: skor ntile + kode + label. Skor R tinggi = baru saja beli
-- (urut recency menurun); skor F dan M tinggi = sering dan besar
-- (urut menaik; id sebagai tie-breaker agar deterministik).
WITH pembeli AS (
    SELECT p.id,
           p.nama,
           (DATE '2026-02-28' - max(e.waktu) FILTER (WHERE e.jenis = 'purchase')::date) AS recency_hari,
           count(*) FILTER (WHERE e.jenis = 'purchase') AS frequency,
           sum(e.nilai) FILTER (WHERE e.jenis = 'purchase') AS monetary
    FROM pengguna p
    JOIN event_web e ON e.pengguna_id = p.id
    GROUP BY p.id, p.nama
    HAVING count(*) FILTER (WHERE e.jenis = 'purchase') > 0
),
ber_skor AS (
    SELECT id,
           nama,
           recency_hari,
           frequency,
           monetary,
           ntile(5) OVER (ORDER BY recency_hari DESC) AS skor_r,
           ntile(5) OVER (ORDER BY frequency ASC, id ASC) AS skor_f,
           ntile(5) OVER (ORDER BY monetary ASC, id ASC) AS skor_m
    FROM pembeli
)
SELECT nama,
       recency_hari,
       frequency,
       monetary,
       skor_r || '-' || skor_f || '-' || skor_m AS kode_rfm,
       CASE
           WHEN skor_r >= 3 AND skor_f >= 3 AND skor_m >= 3 THEN 'Champion'
           WHEN skor_f >= 3 AND skor_m >= 3 THEN 'Pelanggan Setia'
           WHEN skor_r >= 3 AND skor_f <= 2 THEN 'Berpotensi'
           WHEN skor_r <= 2 AND skor_f >= 2 THEN 'Berisiko'
           WHEN skor_r <= 1 THEN 'Nyaris Hilang'
           ELSE 'Perlu Ditinjau'
       END AS segmen
FROM ber_skor
ORDER BY skor_r DESC, skor_f DESC, skor_m DESC, id;

sql
-- Sessionization: kelompokkan event satu pengguna menjadi sesi kunjungan.
-- Batas sesi = jeda ke event sebelumnya lebih dari 30 menit (1800 detik).
-- Nomor sesi = jumlah berjalan (running SUM) atas bendera batas -- pola
-- LAG + SUM yang sama dengan deteksi pulau di chapter sebelumnya,
-- hanya medianya interval waktu, bukan tanggal.

-- Langkah 1: bacaan Budi 10 Februari 2026 -- jeda menit dan bendera batas
-- terlihat telanjang sebelum diagregasi. Tiga sesi terbentuk: pagi (rapat
-- belanja sampai purchase), siang (kembali berkeliling), dan malam.
WITH ber_jarak AS (
    SELECT waktu,
           jenis,
           lag(waktu) OVER (ORDER BY waktu) AS sebelumnya
    FROM event_web
    WHERE pengguna_id = 1
      AND waktu >= TIMESTAMP '2026-02-10 00:00:00'
      AND waktu <  TIMESTAMP '2026-02-11 00:00:00'
)
SELECT waktu,
       jenis,
       sebelumnya,
       round(extract(epoch FROM (waktu - sebelumnya)) / 60)::integer AS jeda_menit,
       CASE
           WHEN sebelumnya IS NULL THEN 1
           WHEN extract(epoch FROM (waktu - sebelumnya)) > 1800 THEN 1
           ELSE 0
       END AS batas_sesi
FROM ber_jarak
ORDER BY waktu;

-- Langkah 2: statistik per sesi untuk seluruh riwayat Budi -- nomor sesi
-- dari running SUM, lalu agregasi per sesi: jumlah event, rentang jam,
-- pembelian, dan nilai belanjanya.
WITH bertanda AS (
    SELECT pengguna_id,
           waktu,
           jenis,
           nilai,
           CASE
               WHEN lag(waktu) OVER w IS NULL THEN 1
               WHEN extract(epoch FROM (waktu - lag(waktu) OVER w)) > 1800 THEN 1
               ELSE 0
           END AS batas_sesi
    FROM event_web
    WHERE pengguna_id = 1
    WINDOW w AS (PARTITION BY pengguna_id ORDER BY waktu)
),
ber_sesi AS (
    SELECT *,
           sum(batas_sesi) OVER (PARTITION BY pengguna_id ORDER BY waktu) AS sesi_ke
    FROM bertanda
)
SELECT sesi_ke,
       date_trunc('day', min(waktu))::date AS tanggal,
       count(*) AS jumlah_event,
       to_char(min(waktu), 'HH24:MI') AS mulai,
       to_char(max(waktu), 'HH24:MI') AS selesai,
       count(*) FILTER (WHERE jenis = 'purchase') AS pembelian,
       coalesce(sum(nilai) FILTER (WHERE jenis = 'purchase'), 0) AS nilai_beli
FROM ber_sesi
GROUP BY sesi_ke
ORDER BY sesi_ke;