Window Functions SQL: ROW_NUMBER, RANK, DENSE_RANK Explained

Apa Itu Window Functions?

Pernah butuh ngitung ranking penjualan tiap toko, tapi tetep mau liat detail per transaksinya? Atau pengen bandingin nilai sekarang dengan nilai sebelumnya di tiap baris?

Ini kerjaan Window Functions.

Window Functions itu fungsi SQL yang ngitung nilai berdasarkan sekumpulan baris (window) yang berhubungan dengan baris saat ini, TANPA menggabungkan baris-baris itu jadi satu kayak GROUP BY.

Bedanya sama GROUP BY: kalau GROUP BY menghasilkan satu baris per grup, Window Functions tetep nampilin semua baris original, tapi dikasih nilai tambahan hasil kalkulasi.

Kenapa Window Functions Penting?

Window Functions itu skill yang wajib dikuasai kalau kamu mau jadi Data Analyst yang lebih advanced. Kenapa?

1. Ranking dan Numbering - Kasih ranking produk terlaris, nomor urut transaksi
2. Running Totals - Hitung kumulatif penjualan dari waktu ke waktu
3. Comparisons - Bandingkan nilai sekarang dengan sebelumnya
4. Moving Averages - Rata-rata bergerak untuk trend analysis
5. Percentiles - Segmentasi data berdasarkan kuartil

Pertanyaan interview kayak "Cari 3 produk terlaris per kategori" atau "Hitung growth rate bulan ke bulan" itu semua butuh Window Functions.

Dataset yang Akan Kita Pakai

Kita pakai dataset penjualan seller di marketplace Indonesia. Bayangin kamu jadi Data Analyst di Tokopedia atau Shopee.

Tabel: penjualan_toko

id	bulan	toko	kategori	total_penjualan	jumlah_order
1	2024-01	Toko Batik Sekar	Fashion	45000000	120
2	2024-01	Warung Kopi Nusantara	Makanan	28000000	350
3	2024-01	Gadget Zone	Elektronik	125000000	85
4	2024-01	Sepatu Bandung	Fashion	52000000	145
5	2024-01	Snack Tradisional	Makanan	15000000	280
6	2024-02	Toko Batik Sekar	Fashion	48000000	130
7	2024-02	Warung Kopi Nusantara	Makanan	32000000	400
8	2024-02	Gadget Zone	Elektronik	115000000	78
9	2024-02	Sepatu Bandung	Fashion	55000000	160
10	2024-02	Snack Tradisional	Makanan	18000000	320
11	2024-03	Toko Batik Sekar	Fashion	52000000	140
12	2024-03	Warung Kopi Nusantara	Makanan	35000000	420
13	2024-03	Gadget Zone	Elektronik	135000000	92
14	2024-03	Sepatu Bandung	Fashion	52000000	155
15	2024-03	Snack Tradisional	Makanan	22000000	380

Sintaks Dasar Window Functions

Struktur dasar Window Functions:

FUNGSI_WINDOW() OVER (
    [PARTITION BY kolom_partisi]
    [ORDER BY kolom_urutan]
    [ROWS/RANGE frame_specification]
)

Komponen penting:
- OVER() - Wajib ada, ini yang bikin fungsi jadi window function
- PARTITION BY - Bagi data jadi grup-grup (opsional)
- ORDER BY - Urutan data dalam window (biasanya wajib untuk ranking)
- ROWS/RANGE - Tentuin batas frame (opsional, untuk running totals dll)

ROW_NUMBER(): Kasih Nomor Urut Unik

ROW_NUMBER() kasih nomor urut 1, 2, 3, ... ke setiap baris. Kalau ada nilai yang sama (tie), tetep dikasih nomor berbeda.

Contoh 1: Nomor Urut Semua Transaksi

SELECT
    bulan,
    toko,
    total_penjualan,
    ROW_NUMBER() OVER (ORDER BY total_penjualan DESC) AS ranking
FROM penjualan_toko;

Hasil:

bulan	toko	total_penjualan	ranking
2024-03	Gadget Zone	135000000	1
2024-01	Gadget Zone	125000000	2
2024-02	Gadget Zone	115000000	3
2024-02	Sepatu Bandung	55000000	4
...	...	...	...

Contoh 2: Ranking per Kategori dengan PARTITION BY

Pola ini yang lebih sering kepake. Kita mau ranking toko di dalam masing-masing kategori.

SELECT
    bulan,
    toko,
    kategori,
    total_penjualan,
    ROW_NUMBER() OVER (
        PARTITION BY kategori
        ORDER BY total_penjualan DESC
    ) AS ranking_dalam_kategori
FROM penjualan_toko;

Hasil:

bulan	toko	kategori	total_penjualan	ranking_dalam_kategori
2024-03	Gadget Zone	Elektronik	135000000	1
2024-01	Gadget Zone	Elektronik	125000000	2
2024-02	Gadget Zone	Elektronik	115000000	3
2024-02	Sepatu Bandung	Fashion	55000000	1
2024-03	Sepatu Bandung	Fashion	52000000	2
2024-03	Toko Batik Sekar	Fashion	52000000	3
...	...	...	...	...

Perhatiin bahwa Sepatu Bandung dan Toko Batik Sekar sama-sama 52 juta di Maret, tapi dapet ranking berbeda (2 dan 3). Itu karena ROW_NUMBER() selalu kasih nomor unik.

Contoh 3: Ambil Top 1 per Kategori per Bulan

Ini pattern yang super sering dipake di interview dan real work.

WITH ranked AS (
    SELECT
        bulan,
        toko,
        kategori,
        total_penjualan,
        ROW_NUMBER() OVER (
            PARTITION BY bulan, kategori
            ORDER BY total_penjualan DESC
        ) AS rn
    FROM penjualan_toko
)
SELECT bulan, toko, kategori, total_penjualan
FROM ranked
WHERE rn = 1
ORDER BY bulan, kategori;

Hasil:

bulan	toko	kategori	total_penjualan
2024-01	Gadget Zone	Elektronik	125000000
2024-01	Sepatu Bandung	Fashion	52000000
2024-01	Warung Kopi Nusantara	Makanan	28000000
2024-02	Gadget Zone	Elektronik	115000000
...	...	...	...

RANK(): Ranking dengan Gap

RANK() mirip ROW_NUMBER(), tapi kalau ada tie (nilai sama), dapet ranking sama. Terus ranking berikutnya loncat sesuai jumlah tie.

Contoh 4: Perbedaan RANK vs ROW_NUMBER

SELECT
    bulan,
    toko,
    kategori,
    total_penjualan,
    ROW_NUMBER() OVER (
        PARTITION BY kategori
        ORDER BY total_penjualan DESC
    ) AS row_num,
    RANK() OVER (
        PARTITION BY kategori
        ORDER BY total_penjualan DESC
    ) AS rank_val
FROM penjualan_toko
WHERE kategori = 'Fashion';

Hasil:

bulan	toko	total_penjualan	row_num	rank_val
2024-02	Sepatu Bandung	55000000	1	1
2024-03	Sepatu Bandung	52000000	2	2
2024-03	Toko Batik Sekar	52000000	3	2
2024-01	Sepatu Bandung	52000000	4	2
2024-02	Toko Batik Sekar	48000000	5	5
2024-01	Toko Batik Sekar	45000000	6	6

Liat perbedaannya? Ada 3 toko dengan 52 juta. ROW_NUMBER kasih 2, 3, 4. Tapi RANK kasih 2, 2, 2. Terus yang berikutnya langsung loncat ke 5, bukan 3.

DENSE_RANK(): Ranking Tanpa Gap

DENSE_RANK() sama kayak RANK(), tapi ga ada gap. Kalau ada tie, ranking berikutnya tetep lanjut dari angka berikutnya.

Contoh 5: Perbandingan ROW_NUMBER, RANK, DENSE_RANK

SELECT
    toko,
    total_penjualan,
    ROW_NUMBER() OVER (ORDER BY total_penjualan DESC) AS row_num,
    RANK() OVER (ORDER BY total_penjualan DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY total_penjualan DESC) AS dense_rank_val
FROM penjualan_toko
WHERE kategori = 'Fashion';

Hasil:

toko	total_penjualan	row_num	rank_val	dense_rank_val
Sepatu Bandung	55000000	1	1	1
Sepatu Bandung	52000000	2	2	2
Toko Batik Sekar	52000000	3	2	2
Sepatu Bandung	52000000	4	2	2
Toko Batik Sekar	48000000	5	5	3
Toko Batik Sekar	45000000	6	6	4

Bedanya RANK dan DENSE_RANK:
- RANK: setelah 2, 2, 2 langsung ke 5
- DENSE_RANK: setelah 2, 2, 2 lanjut ke 3

Kapan Pake Yang Mana?

Fungsi	Use Case
ROW_NUMBER	Butuh nomor urut unik, deduplicate data, pagination
RANK	Ranking dengan gap, kompetisi (ada juara 1 sama, langsung ke juara 3)
DENSE_RANK	Ranking tanpa gap, level/tier system

LAG() dan LEAD(): Akses Baris Sebelum/Sesudah

LAG() ambil nilai dari baris sebelumnya, LEAD() dari baris sesudahnya. Super berguna buat ngitung growth atau perubahan.

Contoh 6: Hitung Growth Penjualan Bulan ke Bulan

SELECT
    bulan,
    toko,
    total_penjualan,
    LAG(total_penjualan) OVER (
        PARTITION BY toko
        ORDER BY bulan
    ) AS penjualan_bulan_lalu,
    total_penjualan - LAG(total_penjualan) OVER (
        PARTITION BY toko
        ORDER BY bulan
    ) AS selisih
FROM penjualan_toko
ORDER BY toko, bulan;

Hasil:

bulan	toko	total_penjualan	penjualan_bulan_lalu	selisih
2024-01	Gadget Zone	125000000	NULL	NULL
2024-02	Gadget Zone	115000000	125000000	-10000000
2024-03	Gadget Zone	135000000	115000000	20000000
2024-01	Sepatu Bandung	52000000	NULL	NULL
2024-02	Sepatu Bandung	55000000	52000000	3000000
2024-03	Sepatu Bandung	52000000	55000000	-3000000
...	...	...	...	...

Contoh 7: Hitung Growth Rate Persentase

SELECT
    bulan,
    toko,
    total_penjualan,
    LAG(total_penjualan) OVER (PARTITION BY toko ORDER BY bulan) AS prev_month,
    ROUND(
        (total_penjualan - LAG(total_penjualan) OVER (PARTITION BY toko ORDER BY bulan))
        * 100.0 / LAG(total_penjualan) OVER (PARTITION BY toko ORDER BY bulan),
        2
    ) AS growth_pct
FROM penjualan_toko
ORDER BY toko, bulan;

Hasil:

bulan	toko	total_penjualan	prev_month	growth_pct
2024-01	Gadget Zone	125000000	NULL	NULL
2024-02	Gadget Zone	115000000	125000000	-8.00
2024-03	Gadget Zone	135000000	115000000	17.39
...	...	...	...	...

SUM() OVER(): Running Total

Salah satu penggunaan paling umum Window Functions adalah running total atau kumulatif.

Contoh 8: Running Total Penjualan per Toko

SELECT
    bulan,
    toko,
    total_penjualan,
    SUM(total_penjualan) OVER (
        PARTITION BY toko
        ORDER BY bulan
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS kumulatif
FROM penjualan_toko
ORDER BY toko, bulan;

Hasil:

bulan	toko	total_penjualan	kumulatif
2024-01	Gadget Zone	125000000	125000000
2024-02	Gadget Zone	115000000	240000000
2024-03	Gadget Zone	135000000	375000000
2024-01	Sepatu Bandung	52000000	52000000
2024-02	Sepatu Bandung	55000000	107000000
2024-03	Sepatu Bandung	52000000	159000000
...	...	...

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW artinya: dari baris pertama sampai baris saat ini.

Contoh 9: Moving Average 3 Bulan

SELECT
    bulan,
    toko,
    total_penjualan,
    ROUND(
        AVG(total_penjualan) OVER (
            PARTITION BY toko
            ORDER BY bulan
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
        0
    ) AS avg_3_bulan
FROM penjualan_toko
ORDER BY toko, bulan;

Hasil:

bulan	toko	total_penjualan	avg_3_bulan
2024-01	Gadget Zone	125000000	125000000
2024-02	Gadget Zone	115000000	120000000
2024-03	Gadget Zone	135000000	125000000
...	...	...	...

NTILE(): Bagi Data jadi N Grup

NTILE(n) bagi data jadi n grup dengan jumlah baris yang kurang lebih sama.

Contoh 10: Bagi Toko jadi 4 Kuartil

SELECT
    toko,
    SUM(total_penjualan) AS total_3_bulan,
    NTILE(4) OVER (ORDER BY SUM(total_penjualan) DESC) AS kuartil
FROM penjualan_toko
GROUP BY toko;

Hasil:

toko	total_3_bulan	kuartil
Gadget Zone	375000000	1
Sepatu Bandung	159000000	1
Toko Batik Sekar	145000000	2
Warung Kopi Nusantara	95000000	3
Snack Tradisional	55000000	4

Kuartil 1 = top 25%, kuartil 4 = bottom 25%.

Window Functions vs GROUP BY

Ini yang sering bikin bingung. Kapan pake mana?

Aspek	GROUP BY	Window Functions
Jumlah baris output	Satu per grup	Semua baris original
Detail per record	Hilang	Tetap ada
Running total	Ga bisa	Bisa
Ranking	Ga bisa langsung	Bisa
Akses baris lain	Ga bisa	Bisa (LAG, LEAD)

Contoh Perbandingan:

-- GROUP BY: Satu baris per kategori
SELECT kategori, SUM(total_penjualan) AS total
FROM penjualan_toko
GROUP BY kategori;

-- Window Function: Semua baris, plus total per kategori
SELECT
    bulan,
    toko,
    kategori,
    total_penjualan,
    SUM(total_penjualan) OVER (PARTITION BY kategori) AS total_kategori
FROM penjualan_toko;

Common Mistakes yang Harus Dihindari

Mistake 1: Lupa ORDER BY di Ranking Functions

-- SALAH: Hasil random
SELECT toko, RANK() OVER () AS ranking FROM penjualan_toko;

-- BENAR
SELECT toko, RANK() OVER (ORDER BY total_penjualan DESC) AS ranking FROM penjualan_toko;

Mistake 2: Salah Paham PARTITION BY

PARTITION BY bukan GROUP BY. Dia cuma bikin window terpisah, bukan menggabungkan baris.

Mistake 3: Ga Paham Frame Specification

Default frame adalah RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Kalau mau running total yang bener, lebih aman pake ROWS explicit.

Tips Praktis

1. Pake CTE untuk Readability

WITH ranked_sales AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY kategori ORDER BY total_penjualan DESC) AS rn
    FROM penjualan_toko
)
SELECT * FROM ranked_sales WHERE rn <= 3;

2. Combine Multiple Window Functions

SELECT
    bulan,
    toko,
    total_penjualan,
    ROW_NUMBER() OVER (ORDER BY total_penjualan DESC) AS overall_rank,
    RANK() OVER (PARTITION BY kategori ORDER BY total_penjualan DESC) AS category_rank,
    SUM(total_penjualan) OVER (PARTITION BY toko ORDER BY bulan) AS running_total
FROM penjualan_toko;

3. Named Windows untuk Reuse

SELECT
    toko,
    total_penjualan,
    ROW_NUMBER() OVER w AS rn,
    RANK() OVER w AS rank_val,
    DENSE_RANK() OVER w AS dense_rank_val
FROM penjualan_toko
WINDOW w AS (ORDER BY total_penjualan DESC);

FAQ

Apa beda ROW_NUMBER dan RANK di SQL?

ROW_NUMBER selalu kasih nomor urut unik ke tiap baris, jadi kalau ada nilai yang sama persis, dia tetap dikasih angka berbeda kayak 1, 2, 3. RANK sebaliknya, kasih ranking yang sama buat nilai yang sama, terus angka berikutnya loncat sesuai jumlah baris yang tie. Contohnya kalau ada 3 baris rank 2, baris selanjutnya langsung rank 5, bukan 3.

Kapan kamu harus pakai PARTITION BY di window function?

Pakai PARTITION BY kalau kamu mau ranking atau perhitungan window-nya dipisah per grup, bukan digabung jadi satu urutan besar. Misalnya kamu mau ranking toko di dalam masing-masing kategori, bukan ranking semua toko sekaligus. Tanpa PARTITION BY, window function bakal ngitung dari seluruh dataset, bukan per grup yang kamu maksud.

Bisa nggak window function dipakai bareng GROUP BY?

Bisa, tapi caranya beda. Window function biasanya dipakai setelah data digrupkan lewat subquery atau CTE, jadi kamu bisa ranking hasil agregasi. GROUP BY sendiri bikin satu baris per grup dan detail per baris hilang. Window function tetap nampilin semua baris asli sambil nambahin kolom hasil kalkulasi, jadi keduanya sering dikombinasikan, bukan saling gantiin.

Kenapa hasil RANK() bisa lompat, misalnya dari 2 langsung ke 5?

Karena RANK() ngitung posisi berdasarkan jumlah baris yang ada di depannya, termasuk yang nilainya sama. Kalau ada 3 baris yang sama-sama dapat rank 2, baris berikutnya otomatis jadi rank 5 karena ada 4 baris di atasnya. Kalau kamu nggak mau ada lompatan kayak gini, pakai DENSE_RANK() yang lanjut urut tanpa gap.

Gimana cara ambil top N data per grup pakai window function?

Bungkus ROW_NUMBER() dengan PARTITION BY kategori atau grup yang kamu mau di dalam CTE, urutkan pakai ORDER BY sesuai metrik yang kamu incar. Habis itu, query dari CTE tadi dan filter baris yang nomornya kurang dari sama dengan N buat ambil sejumlah baris teratas tiap grup. Pola ini yang paling sering dipakai buat cari produk terlaris per kategori atau top customer per cabang.

Kesimpulan

Window Functions itu skill yang bakal sering banget kamu pake sebagai Data Analyst. Inget poin-poin ini:

1. ROW_NUMBER() - Nomor urut unik, bagus buat pagination dan dedupe
2. RANK() - Ranking dengan gap untuk tie
3. DENSE_RANK() - Ranking tanpa gap
4. LAG()/LEAD() - Akses baris sebelum/sesudah untuk growth analysis
5. SUM() OVER() - Running total dan kumulatif
6. PARTITION BY - Bikin window terpisah per grup

Mulai dari contoh simple, terus coba kombinasiin untuk analisis yang lebih kompleks.

Cek nguliksql.id buat latihan lebih banyak.