Latihan Soal SQL Menengah: Window Function & CTE

Latihan soal SQL menengah muter di dua topik: window function dan CTE. Kalau kamu udah bisa GROUP BY dan JOIN tapi mentok pas disuruh bikin ranking per kategori, artikel ini buat kamu.

Delapan soal di bawah pakai dataset toko_berkah, toko online perlengkapan dapur dengan 8.412 order dari 3.190 pelanggan.

Kerjain dulu sendiri. Jawabannya ada di bawah tiap soal, tapi kamu gak bakal jago kalau langsung ngintip.

Skema tabelnya kayak gimana?

orders (id, customer_id, order_date, status, total_belanja)
order_items (id, order_id, produk_id, qty, harga_satuan)
produk (id, nama_produk, kategori, harga, hpp)
customers (id, nama, kota, tgl_daftar)

Semua soal anggap status = 'selesai' itu order yang sah.

Soal 1: Ranking pelanggan berdasarkan total belanja

Tampilkan 10 pelanggan dengan total belanja terbesar, lengkap dengan nomor peringkatnya.

SELECT
  c.nama,
  c.kota,
  SUM(o.total_belanja) AS total,
  RANK() OVER (ORDER BY SUM(o.total_belanja) DESC) AS peringkat
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'selesai'
GROUP BY c.nama, c.kota
ORDER BY total DESC
LIMIT 10;

Yang sering bikin bingung: window function bisa dipakai bareng GROUP BY. Window-nya jalan setelah agregasi selesai, jadi SUM(o.total_belanja) di dalam ORDER BY window itu sah.

Soal 2: Kontribusi tiap kota ke total omzet

Hitung omzet per kota, plus berapa persen kontribusinya ke omzet nasional.

SELECT
  c.kota,
  SUM(o.total_belanja) AS omzet_kota,
  ROUND(100.0 * SUM(o.total_belanja) / SUM(SUM(o.total_belanja)) OVER (), 1) AS persen
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'selesai'
GROUP BY c.kota
ORDER BY omzet_kota DESC;

SUM(SUM(...)) OVER () kelihatan aneh, tapi logikanya sederhana. SUM di dalam ngitung per kota. SUM di luar, yang pakai OVER () tanpa partisi, ngejumlahin semua hasil itu jadi grand total.

Hasilnya di toko_berkah: Jakarta 34,2%, Surabaya 18,7%, Bandung 11,4%. Tiga kota, 64,3% omzet.

Soal 3: Produk terlaris per kategori

Ini soal klasik. Ambil satu produk dengan qty terjual terbanyak di tiap kategori.

WITH penjualan AS (
  SELECT
    p.kategori,
    p.nama_produk,
    SUM(oi.qty) AS total_qty
  FROM order_items oi
  JOIN produk p ON p.id = oi.produk_id
  JOIN orders o ON o.id = oi.order_id
  WHERE o.status = 'selesai'
  GROUP BY p.kategori, p.nama_produk
),
ranking AS (
  SELECT
    kategori,
    nama_produk,
    total_qty,
    ROW_NUMBER() OVER (PARTITION BY kategori ORDER BY total_qty DESC) AS peringkat
  FROM penjualan
)
SELECT kategori, nama_produk, total_qty
FROM ranking
WHERE peringkat = 1
ORDER BY total_qty DESC;

Jebakannya: banyak orang nyoba nulis WHERE ROW_NUMBER() OVER (...) = 1 langsung. Itu error. WHERE jalan sebelum window function dihitung, jadi kolomnya belum ada. Makanya harus dibungkus CTE dulu.

Soal 4: Running total omzet harian

Tampilkan omzet harian bulan Juni plus akumulasinya dari awal bulan.

WITH harian AS (
  SELECT
    order_date::date AS tanggal,
    SUM(total_belanja) AS omzet
  FROM orders
  WHERE status = 'selesai'
    AND order_date >= '2026-06-01'
    AND order_date < '2026-07-01'
  GROUP BY 1
)
SELECT
  tanggal,
  omzet,
  SUM(omzet) OVER (ORDER BY tanggal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS akumulasi
FROM harian
ORDER BY tanggal;

Bagian ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW itu yang bikin totalnya berjalan, bukan sekali jumlah. Tanpa itu, hasilnya sama semua di tiap baris.

Perhatiin juga filter tanggalnya pakai < '2026-07-01', bukan <= '2026-06-30'. Kalau kolomnya timestamp, order jam 14:00 tanggal 30 bakal ilang.

Soal 5: Pertumbuhan omzet bulan ke bulan

Hitung berapa persen omzet naik atau turun dibanding bulan sebelumnya.

WITH omzet_bulanan AS (
  SELECT
    DATE_TRUNC('month', order_date) AS bulan,
    SUM(total_belanja) AS omzet
  FROM orders
  WHERE status = 'selesai'
  GROUP BY 1
)
SELECT
  bulan,
  omzet,
  LAG(omzet) OVER (ORDER BY bulan) AS omzet_bulan_lalu,
  ROUND(100.0 * (omzet - LAG(omzet) OVER (ORDER BY bulan))
    / NULLIF(LAG(omzet) OVER (ORDER BY bulan), 0), 1) AS pertumbuhan_persen
FROM omzet_bulanan
ORDER BY bulan;

LAG ngambil nilai dari baris sebelumnya. Sementara NULLIF(x, 0) di penyebut buat ngindarin error bagi nol. Kalau bulan lalu omzetnya 0, hasilnya jadi NULL, bukan crash.

Soal 6: Pelanggan yang belanja 3 kali berturut-turut

Cari pelanggan yang punya minimal 3 order dengan jarak kurang dari 30 hari antar order.

WITH jarak AS (
  SELECT
    customer_id,
    order_date,
    order_date - LAG(order_date) OVER (
      PARTITION BY customer_id ORDER BY order_date
    ) AS jeda_hari
  FROM orders
  WHERE status = 'selesai'
)
SELECT
  customer_id,
  COUNT(*) AS order_beruntun
FROM jarak
WHERE jeda_hari <= INTERVAL '30 days'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY order_beruntun DESC;

Kenapa HAVING COUNT(*) >= 2 dan bukan 3? Soalnya jeda dihitung antar order. Tiga order beruntun cuma punya dua jeda.

Di toko_berkah ada 218 pelanggan yang lolos filter ini. Mereka nyumbang 22,8% omzet. Pelanggan yang paling layak dikasih perlakuan khusus.

Soal 7: Median belanja per kota

Rata-rata gampang ketarik outlier. Hitung median-nya.

SELECT
  c.kota,
  ROUND(AVG(o.total_belanja)) AS rata_rata,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY o.total_belanja) AS median,
  COUNT(*) AS jumlah_order
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'selesai'
GROUP BY c.kota
HAVING COUNT(*) >= 50
ORDER BY median DESC;

Filter HAVING COUNT(*) >= 50 penting. Kota dengan 3 order gak layak dibandingin, soalnya median dari 3 angka itu gak berarti apa-apa.

Soal 8: Pelanggan yang berhenti belanja

Cari pelanggan yang dulu aktif tapi udah 90 hari gak order. Ini soal yang paling sering keluar di tes kerja.

WITH aktivitas AS (
  SELECT
    customer_id,
    COUNT(*) AS total_order,
    MAX(order_date) AS order_terakhir,
    SUM(total_belanja) AS total_belanja
  FROM orders
  WHERE status = 'selesai'
  GROUP BY customer_id
)
SELECT
  c.nama,
  c.kota,
  a.total_order,
  a.total_belanja,
  CURRENT_DATE - a.order_terakhir::date AS hari_sejak_terakhir
FROM aktivitas a
JOIN customers c ON c.id = a.customer_id
WHERE a.total_order >= 3
  AND a.order_terakhir < CURRENT_DATE - INTERVAL '90 days'
ORDER BY a.total_belanja DESC
LIMIT 20;

Filter total_order >= 3 yang bikin soal ini berguna. Tanpa itu, hasilnya kepenuhan sama orang yang emang cuma nyoba sekali dan gak pernah balik.

Hasil di toko_berkah: 147 pelanggan masuk kategori ini, dengan total belanja historis Rp 218 juta. Itu duit yang lagi berjalan keluar pintu.

Kesalahan umum di soal window function

Filter window di WHERE. Selalu error. Bungkus CTE dulu, filter di luar.

Lupa PARTITION BY. ROW_NUMBER tanpa partisi ngasih nomor 1 sampai N buat seluruh tabel, bukan per grup. Gejala: cuma dapat 1 baris hasil padahal harusnya 1 per kategori.

Ketuker ROW_NUMBER dan RANK. Kalau ada nilai kembar dan kamu pakai RANK, kamu bisa dapat 2 baris "peringkat 1". ROW_NUMBER selalu unik.

Lupa NULLIF pas bagi. Query pertumbuhan yang gak pakai NULLIF bakal crash di bulan pertama, soalnya LAG-nya NULL... atau di bulan yang omzetnya nol.

FAQ

Apa bedanya ROW_NUMBER, RANK, dan DENSE_RANK?

ROW_NUMBER selalu unik. RANK ngasih nomor sama buat nilai kembar tapi ngeloncat (1, 1, 3). DENSE_RANK ngasih nomor sama tanpa loncat (1, 1, 2). Buat ngambil satu baris per grup, pakai ROW_NUMBER.

Kapan harus pakai CTE dan kapan subquery biasa?

Pakai CTE kalau hasilnya dipakai lebih dari sekali, atau kalau query kamu udah tiga tingkat ke dalam. Kalau kamu harus scroll balik buat inget subquery ini ngitung apa, ubah jadi CTE dan kasih nama.

Kenapa window function gak bisa dipakai di WHERE?

Karena urutan eksekusi SQL. WHERE jalan sebelum window dihitung, jadi kolomnya belum ada. Bungkus query-nya pakai CTE, terus filter di query luarnya.

Apakah window function bikin query jadi lambat?

Window butuh sorting, jadi lebih mahal dari agregasi biasa. Di tabel puluhan ribu baris bedanya gak kerasa. Yang bikin lambat itu numpuk 5 window dengan PARTITION BY beda-beda di satu query.

Gimana cara latihan SQL tanpa akses database kantor?

PostgreSQL gratis dan bisa diinstal dalam 10 menit. Kalau males, DB Fiddle di browser udah cukup. Yang penting datanya punya kolom tanggal dan kategori. Dua kolom itu yang bikin soal window function jadi masuk akal.

Penutup

Kalau kamu bisa ngerjain soal 3, 5, dan 8 tanpa ngintip, kamu udah di atas level pemula.

• Pola CTE + ROW_NUMBER + filter peringkat itu jawaban buat 80% soal "top N per grup".
• LAG buat perbandingan antar periode, SUM OVER buat akumulasi.
• Selalu bungkus window di CTE kalau mau difilter.

Sintaks lengkap window function ada di dokumentasi resmi PostgreSQL. Bagian frame clause-nya layak dibaca kalau kamu udah nyaman sama dasarnya.

Mau latihan soal yang lebih banyak dengan feedback langsung? NgulikSQL punya 60+ soal dengan dataset toko_berkah yang sama. Sebelum lanjut, mampir dulu ke referensi ROW_NUMBER dan glossary CTE kalau ada yang masih ngambang.