SQL Window Functions: Panduan Lengkap ROW_NUMBER, RANK, LAG, LEAD

📋 Daftar Isi

Pengenalan Window Functions
Syntax Dasar & OVER Clause
PARTITION BY — Membagi Window
ROW_NUMBER() — Penomoran Baris
RANK() & DENSE_RANK() — Peringkat
LAG() & LEAD() — Akses Baris Lain
NTILE() — Pembagian Kelompok
Frame Clause — ROWS & RANGE
Aggregate Functions sebagai Window
Studi Kasus Nyata
Quiz Pemahaman

1. Pengenalan Window Functions

Window Functions adalah salah satu fitur paling powerful dalam SQL modern. Berbeda dengan fungsi agregat biasa (seperti SUM(), COUNT()) yang mereduksi baris menjadi satu hasil, window functions melakukan perhitungan pada sekumpulan baris tanpa menggabungkannya — setiap baris tetap ada di hasil query.

Bayangkan Anda punya spreadsheet dan ingin menambah kolom "peringkat penjualan" di sebelah data penjualan. Window functions melakukan persis hal itu — menambah kolom hasil perhitungan ke setiap baris, berdasarkan "jendela" (window) data yang Anda definisikan.

Mengapa Window Functions Penting?

Fitur	Aggregate Function	Window Function
Jumlah baris hasil	Dikurangi (per grup jadi 1 baris)	Tetap (semua baris dipertahankan)
Akses kolom lain	Hanya kolom di GROUP BY + agregat	Bisa akses kolom detail + agregat
Contoh penggunaan	Total penjualan per kota	Peringkat penjualan per kota, dengan detail penjualan
Syntax	GROUP BY	OVER (PARTITION BY ...)

Diagram: Perbandingan Aggregate vs Window Function

┌─────────────────────────────────────────────────────────────────┐
│  AGGREGATE (GROUP BY)           WINDOW FUNCTION (OVER)         │
│                                                                 │
│  Tabel Asli:                    Tabel Asli:                     │
│  ┌────────┬───────┬──────┐      ┌────────┬───────┬──────┐      │
│  │ Nama   │ Kota  │ Sales│      │ Nama   │ Kota  │ Sales│      │
│  ├────────┼───────┼──────┤      ├────────┼───────┼──────┤      │
│  │ Ali    │ JKT   │ 100  │      │ Ali    │ JKT   │ 100  │      │
│  │ Budi   │ JKT   │ 200  │      │ Budi   │ JKT   │ 200  │      │
│  │ Citra  │ BDG   │ 150  │      │ Citra  │ BDG   │ 150  │      │
│  │ Dina   │ BDG   │ 250  │      │ Dina   │ BDG   │ 250  │      │
│  └────────┴───────┴──────┘      └────────┴───────┴──────┘      │
│                                                                 │
│  Hasil (2 baris):               Hasil (4 baris + kolom):       │
│  ┌───────┬────────┐             ┌────────┬──────┬─────┬─────┐  │
│  │ Kota  │ Total  │             │ Nama   │ Kota │Sales│Total│  │
│  ├───────┼────────┤             ├────────┼──────┼─────┼─────┤  │
│  │ BDG   │ 400    │             │ Ali    │ JKT  │ 100 │ 300 │  │
│  │ JKT   │ 300    │             │ Budi   │ JKT  │ 200 │ 300 │  │
│  └───────┴────────┘             │ Citra  │ BDG  │ 150 │ 400 │  │
│                                  │ Dina   │ BDG  │ 250 │ 400 │  │
│                                  └────────┴──────┴─────┴─────┘  │
└─────────────────────────────────────────────────────────────────┘

Setup Data Latihan

SQL — Setup Tabel & Data

-- =============================================
-- SETUP DATABASE UNTUK LATIHAN
-- =============================================

-- Tabel karyawan
CREATE TABLE karyawan (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    nama        VARCHAR(100) NOT NULL,
    departemen  VARCHAR(50) NOT NULL,
    posisi      VARCHAR(50),
    gaji        DECIMAL(12,2) NOT NULL,
    tanggal_masuk DATE NOT NULL
);

-- Tabel penjualan
CREATE TABLE penjualan (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    nama_sales  VARCHAR(100) NOT NULL,
    wilayah     VARCHAR(50) NOT NULL,
    bulan       VARCHAR(20) NOT NULL,
    jumlah      INT NOT NULL,
    revenue     DECIMAL(15,2) NOT NULL
);

-- Insert data karyawan
INSERT INTO karyawan (nama, departemen, posisi, gaji, tanggal_masuk) VALUES
('Ali Rahman',     'Engineering', 'Junior Dev',    8000000,  '2024-01-15'),
('Budi Santoso',   'Engineering', 'Senior Dev',    15000000, '2020-03-01'),
('Citra Dewi',     'Marketing',   'Staff',         7000000,  '2023-06-10'),
('Dina Marlina',   'Marketing',   'Manager',       18000000, '2019-08-20'),
('Eka Putra',      'Engineering', 'Junior Dev',    8500000,  '2024-05-01'),
('Fitri Handayani','Finance',     'Staff',         7500000,  '2023-02-15'),
('Gilang Pratama', 'Finance',     'Manager',       17000000, '2020-01-10'),
('Hana Wijaya',    'Engineering', 'Senior Dev',    14000000, '2021-04-25'),
('Indra Kusuma',   'Marketing',   'Staff',         7200000,  '2023-09-01'),
('Joko Susilo',    'Finance',     'Staff',         7800000,  '2022-11-20');

-- Insert data penjualan
INSERT INTO penjualan (nama_sales, wilayah, bulan, jumlah, revenue) VALUES
('Andi',  'Jakarta',  'Januari',   45, 45000000),
('Andi',  'Jakarta',  'Februari',  52, 52000000),
('Andi',  'Jakarta',  'Maret',     38, 38000000),
('Budi',  'Bandung',  'Januari',   60, 60000000),
('Budi',  'Bandung',  'Februari',  55, 55000000),
('Budi',  'Bandung',  'Maret',     70, 70000000),
('Citra', 'Jakarta',  'Januari',   30, 30000000),
('Citra', 'Jakarta',  'Februari',  42, 42000000),
('Citra', 'Jakarta',  'Maret',     50, 50000000),
('Dina',  'Surabaya', 'Januari',   35, 35000000),
('Dina',  'Surabaya', 'Februari',  48, 48000000),
('Dina',  'Surabaya', 'Maret',     55, 55000000);

💡 Kompatibilitas Database

Window functions didukung oleh PostgreSQL (sejak 8.4), MySQL 8.0+, SQL Server, Oracle, dan SQLite 3.25+. Jika masih menggunakan MySQL 5.7 atau lebih lama, Anda perlu upgrade untuk menggunakan fitur ini.

2. Syntax Dasar & OVER Clause

Syntax dasar window function menggunakan clause OVER(). Clause ini mendefinisikan "jendela" (window) data yang akan digunakan untuk perhitungan.

SQL — Syntax Dasar Window Function

-- =============================================
-- SYNTAX UMUM WINDOW FUNCTION
-- =============================================
-- fungsi_window() OVER (
--     [PARTITION BY kolom]
--     [ORDER BY kolom [ASC|DESC]]
--     [frame_clause]
-- )

-- =============================================
-- Contoh paling sederhana: Total keseluruhan
-- =============================================
SELECT
    nama,
    departemen,
    gaji,
    SUM(gaji) OVER() AS total_gaji_seluruh
FROM karyawan;

-- Hasil: setiap baris punya kolom "total_gaji_seluruh"
-- yang berisi SUM dari SEMUA gaji
-- ┌──────────────────┬────────────┬───────────┬──────────────────┐
-- │ nama             │ departemen │ gaji      │ total_gaji_seluruh│
-- ├──────────────────┼────────────┼───────────┼──────────────────┤
-- │ Ali Rahman       │ Engineering│ 8000000   │ 100000000        │
-- │ Budi Santoso     │ Engineering│ 15000000  │ 100000000        │
-- │ Citra Dewi       │ Marketing  │ 7000000   │ 100000000        │
-- │ ... (semua baris punya nilai sama: 100jt)│                  │
-- └──────────────────┴────────────┴───────────┴──────────────────┘


-- =============================================
-- OVER() kosong = window = SELURUH tabel
-- =============================================
SELECT
    nama,
    gaji,
    AVG(gaji) OVER()        AS rata2_seluruh,
    MAX(gaji) OVER()        AS gaji_max_seluruh,
    MIN(gaji) OVER()        AS gaji_min_seluruh,
    COUNT(*) OVER()         AS total_karyawan
FROM karyawan;


-- =============================================
-- Alias Window dengan WINDOW clause (PostgreSQL, MySQL 8+)
-- =============================================
-- Mendefinisikan window sekali, pakai berulang kali
SELECT
    nama,
    departemen,
    gaji,
    AVG(gaji) OVER w AS rata2_dept,
    MAX(gaji) OVER w AS max_dept,
    MIN(gaji) OVER w AS min_dept
FROM karyawan
WINDOW w AS (PARTITION BY departemen);

⚠️ Window Functions Tidak Bisa di WHERE

Window functions tidak bisa digunakan langsung di clause WHERE karena urutan eksekusi SQL. Window functions dieksekusi setelah WHERE, GROUP BY, dan HAVING. Untuk filter hasil window function, gunakan subquery atau CTE (Common Table Expression).

SQL — Filter Hasil Window Function

-- =============================================
-- SALAH: Window function di WHERE (ERROR!)
-- =============================================
-- SELECT nama, gaji, RANK() OVER(ORDER BY gaji DESC) AS ranking
-- FROM karyawan
-- WHERE ranking <= 3;  -- ERROR! "ranking" belum ada di sini

-- =============================================
-- BENAR: Gunakan CTE atau subquery
-- =============================================
-- Metode 1: CTE (Common Table Expression)
WITH ranked AS (
    SELECT
        nama,
        gaji,
        RANK() OVER(ORDER BY gaji DESC) AS ranking
    FROM karyawan
)
SELECT * FROM ranked
WHERE ranking <= 3;

-- Metode 2: Subquery
SELECT * FROM (
    SELECT
        nama,
        gaji,
        RANK() OVER(ORDER BY gaji DESC) AS ranking
    FROM karyawan
) sub
WHERE ranking <= 3;

3. PARTITION BY — Membagi Window

PARTITION BY membagi data menjadi kelompok-kelompok (partisi) sebelum window function dijalankan. Setiap partisi dihitung secara independen — mirip dengan GROUP BY, tetapi tanpa mengurangi baris.

SQL — PARTITION BY

-- =============================================
-- PARTITION BY: Total gaji per departemen
-- =============================================
SELECT
    nama,
    departemen,
    gaji,
    SUM(gaji) OVER(PARTITION BY departemen) AS total_gaji_dept
FROM karyawan;

-- Hasil:
-- ┌──────────────────┬────────────┬───────────┬─────────────────┐
-- │ nama             │ departemen │ gaji      │ total_gaji_dept │
-- ├──────────────────┼────────────┼───────────┼─────────────────┤
-- │ Fitri Handayani  │ Finance    │ 7500000   │ 32300000        │
-- │ Gilang Pratama   │ Finance    │ 17000000  │ 32300000        │
-- │ Joko Susilo      │ Finance    │ 7800000   │ 32300000        │
-- │ Ali Rahman       │ Engineering│ 8000000   │ 45500000        │
-- │ Budi Santoso     │ Engineering│ 15000000  │ 45500000        │
-- │ Eka Putra        │ Engineering│ 8500000   │ 45500000        │
-- │ Hana Wijaya      │ Engineering│ 14000000  │ 45500000        │
-- │ Citra Dewi       │ Marketing  │ 7000000   │ 32200000        │
-- │ Dina Marlina     │ Marketing  │ 18000000  │ 32200000        │
-- │ Indra Kusuma     │ Marketing  │ 7200000   │ 32200000        │
-- └──────────────────┴────────────┴───────────┴─────────────────┘
-- Perhatikan: total_gaji_dept SAMA untuk semua baris dalam departemen!


-- =============================================
-- PARTITION BY + Kolom Tambahan: Persentase
-- =============================================
SELECT
    nama,
    departemen,
    gaji,
    SUM(gaji) OVER(PARTITION BY departemen) AS total_dept,
    ROUND(
        gaji * 100.0 / SUM(gaji) OVER(PARTITION BY departemen), 2
    ) AS persen_dari_dept
FROM karyawan
ORDER BY departemen, gaji DESC;


-- =============================================
-- PARTITION BY dengan Multiple Kolom
-- =============================================
SELECT
    nama_sales,
    wilayah,
    bulan,
    revenue,
    SUM(revenue) OVER(PARTITION BY wilayah) AS total_wilayah,
    SUM(revenue) OVER(PARTITION BY nama_sales) AS total_sales_person,
    SUM(revenue) OVER(PARTITION BY nama_sales, wilayah) AS total_sales_wilayah
FROM penjualan
ORDER BY nama_sales, bulan;

💡 PARTITION BY vs GROUP BY

PARTITION BY mempertahankan semua baris asli — hanya menambah kolom baru. GROUP BY mereduksi baris. Anda bisa menggunakan keduanya sekaligus dalam satu query (GROUP BY di level query, PARTITION BY di window function).

4. ROW_NUMBER() — Penomoran Baris

ROW_NUMBER() menetapkan nomor urut unik ke setiap baris dalam window-nya. Nomor ini dimulai dari 1 dan tidak pernah duplikat — bahkan jika nilai ORDER BY sama.

SQL — ROW_NUMBER()

-- =============================================
-- ROW_NUMBER: Penomoran urut semua karyawan
-- =============================================
SELECT
    ROW_NUMBER() OVER(ORDER BY gaji DESC) AS nomor,
    nama,
    departemen,
    gaji
FROM karyawan;

-- Hasil:
-- ┌──────┬──────────────────┬────────────┬───────────┐
-- │ nomor│ nama             │ departemen │ gaji      │
-- ├──────┼──────────────────┼────────────┼───────────┤
-- │ 1    │ Dina Marlina     │ Marketing  │ 18000000  │
-- │ 2    │ Gilang Pratama   │ Finance    │ 17000000  │
-- │ 3    │ Budi Santoso     │ Engineering│ 15000000  │
-- │ 4    │ Hana Wijaya      │ Engineering│ 14000000  │
-- │ 5    │ Eka Putra        │ Engineering│ 8500000   │
-- │ 6    │ Ali Rahman       │ Engineering│ 8000000   │
-- │ 7    │ Joko Susilo      │ Finance    │ 7800000   │
-- │ 8    │ Fitri Handayani  │ Finance    │ 7500000   │
-- │ 9    │ Indra Kusuma     │ Marketing  │ 7200000   │
-- │ 10   │ Citra Dewi       │ Marketing  │ 7000000   │
-- └──────┴──────────────────┴────────────┴───────────┘


-- =============================================
-- ROW_NUMBER PER PARTISI: Top gaji per departemen
-- =============================================
SELECT
    nama,
    departemen,
    gaji,
    ROW_NUMBER() OVER(
        PARTITION BY departemen
        ORDER BY gaji DESC
    ) AS ranking_dept
FROM karyawan;

-- Setiap departemen punya ranking 1, 2, 3, ... sendiri


-- =============================================
-- USE CASE: Ambil karyawan dengan gaji tertinggi per departemen
-- =============================================
WITH ranked AS (
    SELECT
        nama,
        departemen,
        gaji,
        ROW_NUMBER() OVER(
            PARTITION BY departemen
            ORDER BY gaji DESC
        ) AS rn
    FROM karyawan
)
SELECT nama, departemen, gaji
FROM ranked
WHERE rn = 1;

-- Hasil: 1 orang dengan gaji tertinggi di setiap departemen


-- =============================================
-- USE CASE: Pagination — ambil baris 6-10
-- =============================================
WITH numbered AS (
    SELECT
        ROW_NUMBER() OVER(ORDER BY tanggal_masuk) AS rn,
        nama,
        departemen,
        tanggal_masuk
    FROM karyawan
)
SELECT * FROM numbered
WHERE rn BETWEEN 6 AND 10;


-- =============================================
-- USE CASE: Hapus duplikat (gunakan ROW_NUMBER)
-- =============================================
-- Asumsi ada tabel dengan data duplikat
-- Simpan baris dengan ID terkecil, hapus sisanya

WITH duplicates AS (
    SELECT
        id,
        ROW_NUMBER() OVER(
            PARTITION BY nama, departemen
            ORDER BY id
        ) AS rn
    FROM karyawan
)
DELETE FROM karyawan
WHERE id IN (
    SELECT id FROM duplicates WHERE rn > 1
);

⚠️ ROW_NUMBER vs RANK

ROW_NUMBER() selalu menghasilkan nomor unik (tidak pernah tie). Jika dua baris punya nilai sama, keduanya tetap dapat nomor berbeda. Urutannya tergantung database engine. Jika Anda ingin handling tie, gunakan RANK() atau DENSE_RANK().

5. RANK() & DENSE_RANK() — Peringkat

Berbeda dengan ROW_NUMBER(), RANK() dan DENSE_RANK() bisa menghasilkan peringkat yang sama untuk baris dengan nilai yang sama (tie/imbang).

SQL — RANK vs DENSE_RANK

-- =============================================
-- PERBANDINGAN: ROW_NUMBER vs RANK vs DENSE_RANK
-- =============================================

-- Tambah data dengan gaji yang sama untuk demo
-- (Ali dan Joko punya gaji sama: 8000000 di contoh ini)

SELECT
    nama,
    gaji,
    ROW_NUMBER() OVER(ORDER BY gaji DESC) AS row_num,
    RANK()       OVER(ORDER BY gaji DESC) AS rank_num,
    DENSE_RANK() OVER(ORDER BY gaji DESC) AS dense_rank_num
FROM karyawan;

-- Hasil:
-- ┌──────────────────┬───────────┬─────────┬──────┬──────────────┐
-- │ nama             │ gaji      │ row_num │ rank │ dense_rank   │
-- ├──────────────────┼───────────┼─────────┼──────┼──────────────┤
-- │ Dina Marlina     │ 18000000  │ 1       │ 1    │ 1            │
-- │ Gilang Pratama   │ 17000000  │ 2       │ 2    │ 2            │
-- │ Budi Santoso     │ 15000000  │ 3       │ 3    │ 3            │
-- │ Hana Wijaya      │ 14000000  │ 4       │ 4    │ 4            │
-- │ Eka Putra        │ 8500000   │ 5       │ 5    │ 5            │
-- │ Ali Rahman       │ 8000000   │ 6       │ 6    │ 6            │
-- │ Joko Susilo      │ 8000000   │ 7       │ 6    │ 6    ← tie! │
-- │ Fitri Handayani  │ 7500000   │ 8       │ 8    │ 7            │
-- │ Indra Kusuma     │ 7200000   │ 9       │ 9    │ 8            │
-- │ Citra Dewi       │ 7000000   │ 10      │ 10   │ 9            │
-- └──────────────────┴───────────┴─────────┴──────┴──────────────┘
--
-- Perbedaan kunci (lihat posisi 7-8):
-- ROW_NUMBER: 7, 8  (selalu urut unik)
-- RANK:       6, 8  (loncat ke 8, karena posisi 7 "dipakai" oleh tie)
-- DENSE_RANK: 6, 7  (tidak loncat, langsung ke 7)


-- =============================================
-- USE CASE: Top 3 penjualan per wilayah
-- =============================================
WITH sales_ranked AS (
    SELECT
        nama_sales,
        wilayah,
        bulan,
        revenue,
        DENSE_RANK() OVER(
            PARTITION BY wilayah
            ORDER BY revenue DESC
        ) AS ranking
    FROM penjualan
)
SELECT * FROM sales_ranked
WHERE ranking <= 3
ORDER BY wilayah, ranking;


-- =============================================
-- USE CASE: Tentukan tier/grade gaji
-- =============================================
SELECT
    nama,
    departemen,
    gaji,
    CASE
        WHEN DENSE_RANK() OVER(ORDER BY gaji DESC) <= 3 THEN 'Tier 1 - Top'
        WHEN DENSE_RANK() OVER(ORDER BY gaji DESC) <= 6 THEN 'Tier 2 - Mid'
        ELSE 'Tier 3 - Entry'
    END AS grade_gaji
FROM karyawan
ORDER BY gaji DESC;

Perbedaan RANK vs DENSE_RANK

Aspek	RANK()	DENSE_RANK()
Saat tie	Sama, tapi loncat setelah tie	Sama, langsung lanjut berikutnya
Contoh: 1,1,?	1, 1, 3	1, 1, 2
Cocok untuk	Kompetisi (ada "juara kosong")	Ranking tanpa gap
Total rank unik	Bisa lebih sedikit (ada gap)	Sama dengan jumlah nilai unik

6. LAG() & LEAD() — Akses Baris Lain

LAG() mengakses nilai dari baris sebelumnya, sedangkan LEAD() mengakses nilai dari baris berikutnya. Ini sangat berguna untuk perhitungan selisih, pertumbuhan, dan perbandingan antar periode.

SQL — LAG() & LEAD()

-- =============================================
-- LAG(): Akses nilai baris sebelumnya
-- =============================================
-- Syntax: LAG(kolom, offset, default) OVER(...)

SELECT
    nama_sales,
    bulan,
    revenue,
    LAG(revenue, 1, 0) OVER(
        PARTITION BY nama_sales
        ORDER BY bulan
    ) AS revenue_bulan_lalu
FROM penjualan;

-- Hasil untuk Andi:
-- ┌────────────┬───────────┬──────────┬─────────────────────┐
-- │ nama_sales │ bulan     │ revenue  │ revenue_bulan_lalu  │
-- ├────────────┼───────────┼──────────┼─────────────────────┤
-- │ Andi       │ Februari  │ 52000000 │ 45000000  (Jan)     │
-- │ Andi       │ Januari   │ 45000000 │ 0         (default) │
-- │ Andi       │ Maret     │ 38000000 │ 52000000  (Feb)     │
-- └────────────┴───────────┴──────────┴─────────────────────┘


-- =============================================
-- LEAD(): Akses nilai baris berikutnya
-- =============================================
SELECT
    nama_sales,
    bulan,
    revenue,
    LEAD(revenue, 1, 0) OVER(
        PARTITION BY nama_sales
        ORDER BY bulan
    ) AS revenue_bulan_depan
FROM penjualan;


-- =============================================
-- USE CASE: Hitung pertumbuhan revenue (MoM)
-- =============================================
WITH monthly AS (
    SELECT
        nama_sales,
        bulan,
        revenue,
        LAG(revenue) OVER(
            PARTITION BY nama_sales
            ORDER BY bulan
        ) AS prev_revenue
    FROM penjualan
)
SELECT
    nama_sales,
    bulan,
    revenue,
    prev_revenue,
    revenue - prev_revenue AS selisih,
    CASE
        WHEN prev_revenue IS NULL OR prev_revenue = 0 THEN NULL
        ELSE ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 2)
    END AS pertumbuhan_persen
FROM monthly
ORDER BY nama_sales, bulan;

-- Hasil:
-- ┌────────────┬───────────┬──────────┬────────────┬──────────┬──────────────┐
-- │ nama_sales │ bulan     │ revenue  │ prev_rev   │ selisih  │ growth_%     │
-- ├────────────┼───────────┼──────────┼────────────┼──────────┼──────────────┤
-- │ Andi       │ Januari   │ 45000000 │ NULL       │ NULL     │ NULL         │
-- │ Andi       │ Februari  │ 52000000 │ 45000000   │ 7000000  │ 15.56        │
-- │ Andi       │ Maret     │ 38000000 │ 52000000   │ -14000000│ -26.92       │
-- │ Budi       │ Januari   │ 60000000 │ NULL       │ NULL     │ NULL         │
-- │ Budi       │ Februari  │ 55000000 │ 60000000   │ -5000000 │ -8.33        │
-- │ ...        │ ...       │ ...      │ ...        │ ...      │ ...          │
-- └────────────┴───────────┴──────────┴────────────┴──────────┴──────────────┘


-- =============================================
-- LAG dengan offset > 1
-- =============================================
-- Bandingkan revenue dengan 2 bulan sebelumnya
SELECT
    nama_sales,
    bulan,
    revenue,
    LAG(revenue, 1) OVER w AS bulan_lalu,
    LAG(revenue, 2) OVER w AS dua_bulan_lalu
FROM penjualan
WINDOW w AS (PARTITION BY nama_sales ORDER BY bulan);

Parameter LAG & LEAD

Parameter	Deskripsi	Default
kolom	Kolom yang nilainya diakses	(wajib)
offset	Berapa baris ke belakang/maju	1
default	Nilai jika tidak ada baris (offset melewati batas)	NULL

7. NTILE() — Pembagian Kelompok

NTILE(n) membagi baris menjadi n kelompok yang sama besar dan menetapkan nomor kelompok (1 sampai n) ke setiap baris. Berguna untuk membuat kuartil, persentil, atau kategori.

SQL — NTILE()

-- =============================================
-- NTILE(4): Bagi karyawan jadi 4 kelompok (quartile)
-- =============================================
SELECT
    nama,
    gaji,
    NTILE(4) OVER(ORDER BY gaji DESC) AS quartile
FROM karyawan;

-- Hasil (10 karyawan dibagi 4):
-- ┌──────────────────┬───────────┬──────────┐
-- │ nama             │ gaji      │ quartile │
-- ├──────────────────┼───────────┼──────────┤
-- │ Dina Marlina     │ 18000000  │ 1        │ ← Top 25%
-- │ Gilang Pratama   │ 17000000  │ 1        │
-- │ Budi Santoso     │ 15000000  │ 2        │ ← 25-50%
-- │ Hana Wijaya      │ 14000000  │ 2        │
-- │ Eka Putra        │ 8500000   │ 2        │
-- │ Ali Rahman       │ 8000000   │ 3        │ ← 50-75%
-- │ Joko Susilo      │ 7800000   │ 3        │
-- │ Fitri Handayani  │ 7500000   │ 4        │ ← Bottom 25%
-- │ Indra Kusuma     │ 7200000   │ 4        │
-- │ Citra Dewi       │ 7000000   │ 4        │
-- └──────────────────┴───────────┴──────────┘


-- =============================================
-- NTILE per partisi: Bagi sales per wilayah
-- =============================================
SELECT
    nama_sales,
    wilayah,
    revenue,
    NTILE(2) OVER(
        PARTITION BY wilayah
        ORDER BY revenue DESC
    ) AS tier
FROM penjualan;

-- Setiap wilayah dibagi 2 tier (Top dan Bottom)


-- =============================================
-- USE CASE: Label kategori dengan NTILE + CASE
-- =============================================
SELECT
    nama,
    departemen,
    gaji,
    CASE NTILE(3) OVER(ORDER BY gaji DESC)
        WHEN 1 THEN 'Tinggi'
        WHEN 2 THEN 'Sedang'
        WHEN 3 THEN 'Rendah'
    END AS kategori_gaji
FROM karyawan
ORDER BY gaji DESC;

8. Frame Clause — ROWS & RANGE

Frame clause mendefinisikan secara tepat baris mana saja yang termasuk dalam window. Ini memungkinkan perhitungan seperti running total, moving average, dan cumulative sum.

SQL — Frame Clause

-- =============================================
-- SYNTAX FRAME CLAUSE
-- =============================================
-- ROWS BETWEEN  AND 
-- RANGE BETWEEN  AND 

-- Start/End bisa:
--   UNBOUNDED PRECEDING  — dari awal partisi
--   n PRECEDING          — n baris sebelumnya
--   CURRENT ROW          — baris saat ini
--   n FOLLOWING          — n baris setelahnya
--   UNBOUNDED FOLLOWING  — sampai akhir partisi


-- =============================================
-- RUNNING TOTAL (Kumulatif)
-- =============================================
-- Default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

SELECT
    nama_sales,
    bulan,
    revenue,
    SUM(revenue) OVER(
        PARTITION BY nama_sales
        ORDER BY bulan
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM penjualan
ORDER BY nama_sales, bulan;

-- Hasil untuk Andi:
-- ┌────────────┬───────────┬──────────┬───────────────┐
-- │ nama_sales │ bulan     │ revenue  │ running_total │
-- ├────────────┼───────────┼──────────┼───────────────┤
-- │ Andi       │ Februari  │ 52000000 │ 97000000      │
-- │ Andi       │ Januari   │ 45000000 │ 45000000      │
-- │ Andi       │ Maret     │ 38000000 │ 135000000     │
-- └────────────┴───────────┴──────────┴───────────────┘


-- =============================================
-- MOVING AVERAGE (Rata-rata Bergerak)
-- =============================================
-- Rata-rata dari 3 baris: baris sebelumnya + saat ini + berikutnya

SELECT
    nama_sales,
    bulan,
    revenue,
    AVG(revenue) OVER(
        PARTITION BY nama_sales
        ORDER BY bulan
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_3
FROM penjualan
ORDER BY nama_sales, bulan;


-- =============================================
-- RUNNING MAX dan MIN
-- =============================================
SELECT
    nama_sales,
    bulan,
    revenue,
    MAX(revenue) OVER(
        PARTITION BY nama_sales
        ORDER BY bulan
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS best_so_far,
    MIN(revenue) OVER(
        PARTITION BY nama_sales
        ORDER BY bulan
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS worst_so_far
FROM penjualan
ORDER BY nama_sales, bulan;


-- =============================================
-- PERCENTILE dengan frame
-- =============================================
SELECT
    nama_sales,
    bulan,
    revenue,
    SUM(revenue) OVER() AS grand_total,
    ROUND(
        SUM(revenue) OVER(
            PARTITION BY nama_sales
            ORDER BY bulan
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) * 100.0 / SUM(revenue) OVER(), 2
    ) AS cumulative_pct
FROM penjualan
ORDER BY nama_sales, bulan;

ROWS vs RANGE

Tipe	Deskripsi	Cocok Untuk
ROWS	Berdasarkan jumlah baris fisik	Moving average, running total
RANGE	Berdasarkan nilai ORDER BY	Window berdasarkan nilai/logika

💡 Default Frame

Jika Anda menulis ORDER BY di dalam OVER() tanpa frame clause, defaultnya adalah RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Ini berbeda dari ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — perbedaannya terlihat saat ada nilai duplikat di kolom ORDER BY.

9. Aggregate Functions sebagai Window

Semua fungsi agregat standar — SUM(), AVG(), COUNT(), MIN(), MAX() — bisa digunakan sebagai window function dengan menambahkan OVER().

SQL — Aggregate sebagai Window Function

-- =============================================
-- Analisis Lengkap dengan Multiple Windows
-- =============================================
SELECT
    nama,
    departemen,
    gaji,
    -- Perbandingan dengan rata-rata departemen
    AVG(gaji) OVER(PARTITION BY departemen) AS avg_dept,
    gaji - AVG(gaji) OVER(PARTITION BY departemen) AS selisih_avg,

    -- Perbandingan dengan rata-rata keseluruhan
    AVG(gaji) OVER() AS avg_total,

    -- Persentil dalam departemen
    COUNT(*) OVER(PARTITION BY departemen) AS jumlah_dept,
    ROUND(
        COUNT(*) OVER(
            PARTITION BY departemen
            ORDER BY gaji DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) * 100.0 / COUNT(*) OVER(PARTITION BY departemen), 0
    ) AS persentil_dept,

    -- Gap dengan gaji tertinggi di departemen
    MAX(gaji) OVER(PARTITION BY departemen) - gaji AS gap_max_dept,

    -- Gap dengan gaji terendah di departemen
    gaji - MIN(gaji) OVER(PARTITION BY departemen) AS gap_min_dept
FROM karyawan
ORDER BY departemen, gaji DESC;


-- =============================================
-- COUNT sebagai Window: Total per partisi
-- =============================================
SELECT
    nama_sales,
    wilayah,
    bulan,
    revenue,
    COUNT(*) OVER(PARTITION BY wilayah) AS total_transaksi_wilayah,
    COUNT(DISTINCT nama_sales) OVER(PARTITION BY wilayah) AS unique_sales_wilayah
FROM penjualan;


-- =============================================
-- Conditional Aggregation + Window Function
-- =============================================
SELECT
    nama,
    departemen,
    gaji,
    -- Persentase gaji terhadap total departemen
    ROUND(
        gaji * 100.0 / SUM(gaji) OVER(PARTITION BY departemen), 2
    ) AS pct_of_dept,
    -- Kumulatif departemen (urut gaji DESC)
    SUM(gaji) OVER(
        PARTITION BY departemen
        ORDER BY gaji DESC
    ) AS cumulative_dept
FROM karyawan
ORDER BY departemen, gaji DESC;

10. Studi Kasus Nyata

Studi Kasus 1: Analisis Penjualan E-Commerce

SQL — Studi Kasus E-Commerce

-- =============================================
-- Tabel: orders (pesanan e-commerce)
-- =============================================
-- Analisis: Top 3 produk per kategori berdasarkan revenue

WITH product_revenue AS (
    SELECT
        p.nama_produk,
        p.kategori,
        SUM(dp.subtotal) AS total_revenue,
        SUM(dp.jumlah)   AS total_qty,
        ROW_NUMBER() OVER(
            PARTITION BY p.kategori
            ORDER BY SUM(dp.subtotal) DESC
        ) AS rank_in_category
    FROM detail_pesanan dp
    JOIN produk p ON dp.id_produk = p.id_produk
    JOIN pesanan ps ON dp.id_pesanan = ps.id_pesanan
    WHERE ps.status = 'selesai'
    GROUP BY p.nama_produk, p.kategori
)
SELECT
    nama_produk,
    kategori,
    total_revenue,
    total_qty,
    rank_in_category
FROM product_revenue
WHERE rank_in_category <= 3
ORDER BY kategori, rank_in_category;


-- =============================================
-- Analisis: Trend penjualan bulanan + pertumbuhan
-- =============================================
WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(tanggal_pesanan, '%Y-%m') AS bulan,
        COUNT(*) AS jumlah_order,
        SUM(total) AS revenue,
        AVG(total) AS avg_order_value
    FROM pesanan
    WHERE status != 'batal'
    GROUP BY DATE_FORMAT(tanggal_pesanan, '%Y-%m')
)
SELECT
    bulan,
    jumlah_order,
    revenue,
    avg_order_value,
    LAG(revenue) OVER(ORDER BY bulan) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER(ORDER BY bulan)) * 100.0
        / NULLIF(LAG(revenue) OVER(ORDER BY bulan), 0), 2
    ) AS revenue_growth_pct,
    SUM(revenue) OVER(ORDER BY bulan) AS cumulative_revenue,
    AVG(revenue) OVER(
        ORDER BY bulan
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM monthly_sales
ORDER BY bulan;

Studi Kasus 2: Analisis HR — Distribusi Gaji

SQL — Studi Kasus HR

-- =============================================
-- Dashboard HR: Lengkap dengan window functions
-- =============================================
WITH salary_analysis AS (
    SELECT
        nama,
        departemen,
        posisi,
        gaji,
        tanggal_masuk,
        -- Peringkat dalam departemen
        DENSE_RANK() OVER(
            PARTITION BY departemen
            ORDER BY gaji DESC
        ) AS rank_dept,

        -- Quartile keseluruhan
        NTILE(4) OVER(ORDER BY gaji DESC) AS quartile,

        -- Running count berdasarkan tanggal masuk
        ROW_NUMBER() OVER(
            ORDER BY tanggal_masuk
        ) AS urutan_masuk,

        -- Bandingkan dengan rata-rata
        gaji - AVG(gaji) OVER() AS selisih_avg_global,
        gaji - AVG(gaji) OVER(PARTITION BY departemen) AS selisih_avg_dept,

        -- Persentase dari max gaji departemen
        ROUND(
            gaji * 100.0 / MAX(gaji) OVER(PARTITION BY departemen), 2
        ) AS pct_of_max_dept,

        -- Total karyawan di departemen
        COUNT(*) OVER(PARTITION BY departemen) AS total_anggota
    FROM karyawan
)
SELECT
    nama,
    departemen,
    posisi,
    gaji,
    rank_dept,
    CASE quartile
        WHEN 1 THEN 'Q1 (Top 25%)'
        WHEN 2 THEN 'Q2 (25-50%)'
        WHEN 3 THEN 'Q3 (50-75%)'
        WHEN 4 THEN 'Q4 (Bottom 25%)'
    END AS salary_quartile,
    selisih_avg_dept,
    pct_of_max_dept,
    total_anggota
FROM salary_analysis
ORDER BY departemen, gaji DESC;

Studi Kasus 3: Cohort Analysis

SQL — Cohort Analysis

-- =============================================
-- Cohort Analysis: Retensi pelanggan per bulan
-- =============================================
WITH customer_cohort AS (
    SELECT
        c.id_pelanggan,
        DATE_FORMAT(c.tanggal_daftar, '%Y-%m') AS cohort_month,
        DATE_FORMAT(p.tanggal_pesanan, '%Y-%m') AS order_month,
        -- Hitung bulan ke-n sejak daftar
        TIMESTAMPDIFF(MONTH,
            c.tanggal_daftar,
            p.tanggal_pesanan
        ) AS months_since_signup
    FROM pelanggan c
    JOIN pesanan p ON c.id_pelanggan = p.id_pelanggan
    WHERE p.status != 'batal'
),
cohort_size AS (
    SELECT
        cohort_month,
        COUNT(DISTINCT id_pelanggan) AS cohort_customers
    FROM customer_cohort
    GROUP BY cohort_month
)
SELECT
    cc.cohort_month,
    cs.cohort_customers,
    cc.months_since_signup,
    COUNT(DISTINCT cc.id_pelanggan) AS active_customers,
    ROUND(
        COUNT(DISTINCT cc.id_pelanggan) * 100.0 / cs.cohort_customers, 2
    ) AS retention_pct
FROM customer_cohort cc
JOIN cohort_size cs ON cc.cohort_month = cs.cohort_month
GROUP BY cc.cohort_month, cs.cohort_customers, cc.months_since_signup
ORDER BY cc.cohort_month, cc.months_since_signup;

Tips Performa Window Functions

Tips	Penjelasan
Gunakan PARTITION BY yang terindeks	Database lebih cepat memproses partisi yang terindeks
Minimalkan jumlah window functions	Setiap OVER() = satu pass data. Gabungkan jika PARTITION BY dan ORDER BY sama
Gunakan WINDOW clause	Definisikan window sekali, pakai berulang kali — lebih clean dan efisien
Filter dulu, window sesudahnya	Gunakan WHERE sebelum window function untuk mengurangi data yang diproses
Hindari ORDER BY tanpa tujuan	ORDER BY di OVER() tanpa frame clause = default RANGE, yang bisa lambat

11. Quiz: Uji Pemahamanmu!

Setelah membaca tutorial di atas, jawablah 5 pertanyaan berikut untuk menguji pemahamanmu tentang SQL Window Functions:

Pertanyaan 1: Apa perbedaan utama antara RANK() dan DENSE_RANK()?

a) RANK() lebih cepat dari DENSE_RANK()

b) DENSE_RANK() tidak punya gap setelah tie, RANK() punya gap

c) RANK() hanya bisa di PostgreSQL, DENSE_RANK() universal

d) Tidak ada perbedaan, keduanya identik

Pertanyaan 2: Fungsi apa yang digunakan untuk mengakses nilai baris sebelumnya?

a) LEAD()

b) PREVIOUS()

c) LAG()

d) BEFORE()

Pertanyaan 3: Mengapa window functions tidak bisa digunakan di clause WHERE?

a) Karena window functions hanya untuk PostgreSQL

b) Karena window functions dieksekusi setelah WHERE dalam urutan SQL

c) Karena window functions membutuhkan HAVING

d) Sebenarnya bisa, tidak ada batasan

Pertanyaan 4: Apa fungsi dari NTILE(4)?

a) Mengambil 4 baris pertama

b) Membagi baris menjadi 4 kelompok (quartile)

c) Menampilkan 4 kolom terakhir

d) Mengurutkan 4 baris secara descending

Pertanyaan 5: Untuk membuat moving average 3 bulan, frame clause yang tepat adalah?

a) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

b) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

c) ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

d) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING