Belajar Data Analyst - SQL Lanjutan & Window Functions

#Pendahuluan

Setelah di episode 3 kalian menguasai SELECT, JOIN, dan GROUP BY, sekarang saatnya naik satu level: analisis yang membutuhkan perbandingan antar baris — seperti "peringkat produk terlaris di tiap kategori", "selisih penjualan bulan ini vs bulan lalu", atau "retention pelanggan per bulan". Query jenis ini seringkali mustahil atau sangat berbelit jika hanya mengandalkan GROUP BY.

Jawabannya adalah window functions: fungsi yang menghitung nilai per baris sambil tetap mempertahankan detail tiap baris — berbeda dengan GROUP BY yang menggabungkan baris. Ditambah CTE untuk menulis query bertingkat yang rapi dan mudah dibaca, plus dasar optimasi query, episode ini akan mengubah kalian dari penulis query menjadi query thinker.

#Window Functions: Agregasi Tanpa Kehilangan Detail

Sintaks dasarnya: FUNCTION() OVER (PARTITION BY ... ORDER BY ...). Berbeda dengan GROUP BY yang menciutkan banyak baris jadi satu, window function mengembalikan nilai agregat di setiap baris.

SELECT id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS total_per_customer
FROM orders;

Setiap baris tetap utuh, tetapi bertambah satu kolom total_per_customer yang dihitung per customer_id.

#ROW_NUMBER, RANK, dan DENSE_RANK

Ketiganya memberi nomor urut di dalam partisi, dengan perbedaan penting pada data yang seri (tie):

Klasik: top 3 produk per kategori.

SELECT kategori, product, jumlah, peringkat
FROM (
    SELECT category AS kategori,
           product,
           COUNT(*) AS jumlah,
           ROW_NUMBER() OVER (
               PARTITION BY category
               ORDER BY COUNT(*) DESC
           ) AS peringkat
    FROM orders
    GROUP BY category, product
) t
WHERE peringkat <= 3;

#LAG dan LEAD: Melihat Baris Sebelum/Sesudah

LAG mengambil nilai dari baris sebelumnya, LEAD dari baris berikutnya. Ini kunci untuk analisis tren.

WITH monthly AS (
    SELECT DATE_TRUNC('month', ordered_at) AS bulan,
           COUNT(*) AS jumlah
    FROM orders
    GROUP BY DATE_TRUNC('month', ordered_at)
)
SELECT bulan,
       jumlah,
       LAG(jumlah) OVER (ORDER BY bulan) AS bulan_lalu,
       jumlah - LAG(jumlah) OVER (ORDER BY bulan) AS selisih
FROM monthly
ORDER BY bulan;

LAG memungkinkan perbandingan periode — pola yang sama dipakai untuk menghitung pertumbuhan month-over-month (MoM) dan year-over-year (YoY).

#CTE: Query Bertingkat yang Rapi

CTE (Common Table Expression) — ditulis dengan WITH — membuat subquery diberi nama sehingga query panjang menjadi modular dan mudah dibaca. Contoh di atas sudah memakai CTE bernama monthly. Keunggulan CTE: bisa digunakan berulang dalam satu query, dan bisa mereferensikan dirinya sendiri (recursive CTE untuk hierarki).

WITH revenue_per_customer AS (
    SELECT customer_id, SUM(amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
segmented AS (
    SELECT customer_id,
           revenue,
           CASE
               WHEN revenue >= 10000000 THEN 'high'
               WHEN revenue >= 1000000 THEN 'mid'
               ELSE 'low'
           END AS segmen
    FROM revenue_per_customer
)
SELECT segmen, COUNT(*) AS jumlah_customer
FROM segmented
GROUP BY segmen
ORDER BY jumlah_customer DESC;

CTE mengubah "piramida query" menjadi alur yang dibaca dari atas ke bawah — jauh lebih mudah di-review oleh rekan kerja dan oleh kalian sendiri tiga bulan kemudian.

#Optimasi Query: Kenali EXPLAIN

Ketika query berjalan lambat di dataset besar, EXPLAIN adalah cermin ajaib yang menunjukkan bagaimana database mengeksekusi query kalian.

EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM orders
WHERE ordered_at >= '2025-11-01'
GROUP BY customer_id;

Output menampilkan tahapan eksekusi, jumlah baris yang dipindai, dan waktu tiap langkah. Tiga hal yang paling sering kalian lihat:

Prinsip optimasi nomor satu: kurangi data sejak dini — filter di WHERE sebelum join, sebelum agregasi. Database bisa membaca jutaan baris dalam sekejap, tetapi membaca miliaran baris tetap terasa.

#Praktik: Analisis Cohort & Retention

Analisis cohort mengelompokkan pelanggan berdasarkan periode akuisisi pertama (misal bulan join), lalu melacak perilaku mereka di bulan-bulan berikutnya. Tujuannya: tahu apakah pelanggan yang datang bulan Mei tetap aktif seperti yang datang bulan Januari.

WITH first_order AS (
    SELECT customer_id,
           DATE_TRUNC('month', MIN(ordered_at)) AS cohort
    FROM orders
    GROUP BY customer_id
),
monthly_active AS (
    SELECT customer_id,
           DATE_TRUNC('month', ordered_at) AS bulan
    FROM orders
    GROUP BY customer_id, DATE_TRUNC('month', ordered_at)
)
SELECT f.cohort,
       COUNT(DISTINCT f.customer_id) AS ukuran_cohort,
       COUNT(DISTINCT CASE WHEN m.bulan = f.cohort + INTERVAL '1 month' THEN f.customer_id END) AS bulan_1,
       COUNT(DISTINCT CASE WHEN m.bulan = f.cohort + INTERVAL '2 month' THEN f.customer_id END) AS bulan_2,
       COUNT(DISTINCT CASE WHEN m.bulan = f.cohort + INTERVAL '3 month' THEN f.customer_id END) AS bulan_3
FROM first_order f
LEFT JOIN monthly_active m ON m.customer_id = f.customer_id
GROUP BY f.cohort
ORDER BY f.cohort;

Retention dihitung sebagai (customer aktif di bulan N) dibagi (ukuran cohort). Episode 14 akan membedah cohort analysis lebih dalam — termasuk retention curve dan lifetime value.

#Kesalahan Umum

• Tertukar RANK vs ROW_NUMBER. Jika ingin peringkat dengan angka seri, pakai RANK/DENSE_RANK; jika butuh nomor baris unik, pakai ROW_NUMBER.
• Lupa PARTITION BY. Tanpa partisi, window function menghitung di seluruh tabel — biasanya bukan yang kalian mau.
• Membandingkan LAG tanpa urutan. ORDER BY di dalam OVER menentukan baris mana yang "sebelum". Selalu tetapkan urutannya.
• Menulis query raksasa tanpa CTE. Bukan hanya soal keindahan — CTE membuat debug dan review menjadi mungkin.

#Penutup

Inti yang harus dibawa pulang:

• Window functions menghitung agregat per baris tanpa menciutkan detail: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD.
• CTE (WITH) membuat query bertingkat modular, rapi, dan mudah di-debug.
• EXPLAIN ANALYZE adalah titik awal optimasi; kurangi data sejak dini.
• Cohort & retention dibangun dari kombinasi DATE_TRUNC, window, dan CTE.

Di episode 5 selanjutnya kita beralih dari SQL ke statistika dasar — mean, median, mode, variance, distribusi, dan sampling — plus cara merangkum dan menginterpretasi dataset tanpa terjebak kesimpulan yang menyesatkan. Pastikan tetap semangat, karena statistika adalah bahasa di balik semua insight!