SQL untuk Data Analyst: Window Functions, CTE, dan Query Tingkat Lanjut

SQL window functions, CTE (Common Table Expressions), dan pola query tingkat lanjut merupakan fondasi utama dalam SQL analitik. Baik untuk mempersiapkan wawancara data analyst maupun menangani query pelaporan yang kompleks, teknik-teknik ini mengubah subquery yang bertele-tele dan sulit dibaca menjadi SQL yang bersih serta berperforma tinggi.

Memahami SQL Window Functions dan Klausa OVER

Window function menerapkan perhitungan pada sebuah "jendela" (window) dari baris-baris data. Klausa OVER mengontrol baris mana saja yang masuk ke dalam jendela tersebut dan bagaimana urutannya. Berbeda dengan fungsi agregat yang menggunakan GROUP BY, window functions mempertahankan setiap baris individual dalam result set.

Sintaks umumnya mengikuti pola yang konsisten di semua database utama — PostgreSQL, MySQL 8+, BigQuery, SQL Server, dan Snowflake.

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  -- Ranks employees within each department by salary
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  -- Running total of salaries within department
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

PARTITION BY memecah data menjadi kelompok-kelompok (mirip GROUP BY tetapi tanpa menggabungkan baris). ORDER BY mendefinisikan urutan dalam setiap partisi. Klausa frame opsional (ROWS BETWEEN ...) mempersempit jendela ke rentang baris tertentu.

Perbandingan ROW_NUMBER, RANK, dan DENSE_RANK

Ketiga fungsi peringkat ini terlihat serupa namun berperilaku berbeda ketika terdapat nilai yang sama (ties). Memilih fungsi yang salah merupakan sumber bug yang sering terjadi dalam query analitik.

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

ROW_NUMBER selalu memberikan bilangan bulat berurutan yang unik — bahkan untuk nilai yang sama, satu baris secara arbitrer mendapatkan nomor yang lebih rendah. RANK memberikan nomor yang sama untuk nilai identik tetapi melewatkan nomor berikutnya (1, 1, 3). DENSE_RANK juga menangani nilai yang sama tetapi tidak pernah melewatkan nomor (1, 1, 2). Untuk query top-N di mana duplikat harus berbagi posisi yang sama, DENSE_RANK biasanya merupakan pilihan yang tepat.

LAG dan LEAD untuk Analisis Periode-ke-Periode

LAG dan LEAD mengakses data dari baris sebelumnya atau berikutnya tanpa perlu melakukan self-join. Kedua fungsi ini sangat penting untuk menghitung pertumbuhan periode-ke-periode, mendeteksi tren, dan mengidentifikasi perubahan.

-- period_over_period.sql
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

Argumen kedua pada LAG/LEAD menentukan offset (default-nya adalah 1). Argumen ketiga yang bersifat opsional menyediakan nilai default ketika tidak ada baris pada offset tersebut — berguna untuk menghindari NULL pada baris pertama atau terakhir. NULLIF dalam perhitungan pertumbuhan mencegah kesalahan pembagian dengan nol.

NTILE dan Pengelompokan Persentil

NTILE mendistribusikan baris ke dalam sejumlah kelompok yang kurang lebih sama rata. Data analyst menggunakannya untuk analisis kuartil, penilaian desil, dan segmentasi pelanggan.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

Kuartil 1 berisi pelanggan dengan pengeluaran tertinggi, kuartil 4 yang terendah. Pola ini langsung memetakan ke segmentasi RFM (Recency, Frequency, Monetary) yang digunakan dalam analitik pemasaran.

Common Table Expressions: Menggantikan Subquery Bersarang

CTE (klausa WITH) memecah query kompleks menjadi langkah-langkah bernama yang mudah dibaca. Setiap CTE berfungsi sebagai result set sementara bernama yang hanya ada selama durasi query tersebut. Selain meningkatkan keterbacaan, CTE juga mempermudah proses debugging — setiap langkah dapat diuji secara independen.

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

Pendekatan tiga langkah ini menggantikan subquery bersarang yang dalam. Setiap CTE memiliki tanggung jawab yang jelas: agregasi, peringkat, dan penyaringan.

Recursive CTE untuk Data Hierarkis

Recursive CTE memecahkan masalah yang melibatkan data hierarkis atau berbentuk graf — struktur organisasi, pohon kategori, bill-of-materials, dan query pencarian jalur.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

Base case memilih node akar (karyawan tanpa manajer). Recursive case melakukan join kembali ke CTE itu sendiri, membangun hierarki level demi level. Kolom management_chain menggabungkan nama-nama untuk menunjukkan jalur pelaporan lengkap. Sebagian besar database membatasi kedalaman rekursi untuk mencegah infinite loop — PostgreSQL secara default membatasi hingga 100 iterasi.

Pola Analitik Lanjutan: Gaps, Islands, dan Running Totals

Pola gaps-and-islands mengidentifikasi urutan berturut-turut dalam data — periode langganan aktif, hari login berurutan, atau periode produksi tanpa gangguan. Teknik ini menggabungkan ROW_NUMBER dengan aritmatika tanggal.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

Inti dari teknik ini adalah: untuk tanggal yang berurutan, mengurangkan nomor baris yang terus bertambah dari tanggal selalu menghasilkan nilai yang sama. Ketika terjadi celah (gap), nilai yang dihasilkan bergeser dan membentuk kelompok baru. Query ini menemukan semua streak login dengan 3 hari berturut-turut atau lebih untuk setiap pengguna.

Menggabungkan Window Functions dengan CASE untuk Analitik Kondisional

Query analitik di dunia nyata sering menggabungkan window functions dengan ekspresi CASE untuk menghitung metrik kondisional dalam satu query yang sama.

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

Satu query ini menghitung rata-rata bergerak 7 hari, total kumulatif quarter-to-date yang direset setiap kuartal, dan flag outlier — semuanya tanpa subquery atau self-join.

Optimasi Performa untuk Query Analitik

Window functions dan CTE merupakan fitur yang sangat powerful tetapi dapat menjadi bottleneck pada tabel berukuran besar. Beberapa teknik dapat membantu menjaga performa pada skala besar.

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

Klausa WINDOW (didukung di PostgreSQL, MySQL 8+, dan BigQuery) mendefinisikan window sekali dan menggunakannya kembali di beberapa fungsi. Pendekatan ini meningkatkan keterbacaan sekaligus berpotensi mengoptimalkan query plan.

Beberapa strategi tambahan untuk meningkatkan performa query analitik meliputi: memfilter data pada klausa WHERE sebelum evaluasi window function, memastikan kolom yang digunakan dalam PARTITION BY dan ORDER BY memiliki indeks yang sesuai, dan membatasi jumlah baris yang diproses sedini mungkin dalam pipeline query. Pada tabel dengan jutaan baris, penggunaan indeks yang tepat pada kolom partisi dapat mengurangi waktu eksekusi secara signifikan.

Selain itu, perlu diperhatikan bahwa materialized CTE (sebagaimana diterapkan di PostgreSQL) menyimpan hasil sementara dalam memori atau disk. Untuk CTE yang menghasilkan result set besar tetapi hanya sebagian kecil yang digunakan pada langkah berikutnya, pertimbangkan untuk menambahkan filter sedini mungkin agar data yang dimaterialisasi tetap minimal.

Kesimpulan

• Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) beroperasi pada sekumpulan baris tanpa menggabungkannya — berbeda dengan agregat GROUP BY
• CTE memecah query kompleks menjadi langkah-langkah bernama yang dapat diuji dan menggantikan subquery bersarang yang dalam
• Recursive CTE menangani data hierarkis seperti struktur organisasi dan pohon kategori, tetapi membutuhkan batasan kedalaman dan indeks pada kolom join
• Teknik gaps-and-islands (ROW_NUMBER + aritmatika tanggal) mengidentifikasi urutan berturut-turut dalam data time-series
• Named windows (klausa WINDOW) mengurangi duplikasi kode dan dapat meningkatkan optimasi query plan
• Memfilter data dalam klausa WHERE sebelum evaluasi window function sangat penting untuk menjaga performa pada tabel berukuran besar
• Pola-pola ini sering muncul dalam wawancara data analytics dan langsung dapat diterapkan pada pelaporan harian, segmentasi, dan analisis tren