Analytical Queries

Seluruh peralatan advanced-sql yang telah kita kumpulkan — CTE beruntun, window function, LATERAL, pola upsert — bermuara pada satu tujuan praktis: menjawab pertanyaan bisnis dari data mentah dalam satu query yang terbaca. Pada chapter penutup ini kita merakit laporan-laporan analitik yang lazim diminta setiap aplikasi: perbandingan penjualan antar tahun, kontribusi kumulatif produk terhadap total, klasifikasi top-N plus kategori “lainnya”, dan analisis kohort sederhana untuk melihat pelanggan yang kembali. Setiap laporan dibangun bertahap dengan CTE yang bisa diperiksa satu per satu, sehingga query panjang tetap dapat diaudit baris demi baris.

Menyiapkan Tabel Latihan #

Data latihan kita berupa penjualan dua tahun — 2025 dan 2026 — dengan beberapa kategori produk. Nilai-nilainya dijaga kecil agar setiap angka laporan bisa dihitung ulang secara manual:

sql
DROP DATABASE IF EXISTS belajar_adv;
CREATE DATABASE belajar_adv;
USE belajar_adv;

Output yang Dihasilkan:

text
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

sql
CREATE TABLE penjualan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tanggal DATE NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  produk VARCHAR(50) NOT NULL,
  jumlah INT NOT NULL,
  harga DECIMAL(10,2) NOT NULL
);

INSERT INTO penjualan (tanggal, kategori, produk, jumlah, harga) VALUES
  ('2025-01-10', 'kopi', 'Kopi Gayo', 4, 55000.00),
  ('2025-02-15', 'kopi', 'Kopi Toraja', 2, 62000.00),
  ('2025-03-20', 'teh', 'Teh Melati', 6, 25000.00),
  ('2025-04-11', 'alat', 'Grinder Manual', 1, 350000.00),
  ('2025-05-09', 'kopi', 'Kopi Gayo', 3, 55000.00),
  ('2025-06-17', 'teh', 'Teh Hijau', 5, 28000.00),
  ('2025-07-23', 'kopi', 'Kopi Robusta', 2, 48000.00),
  ('2025-08-14', 'alat', 'French Press', 2, 180000.00),
  ('2025-09-03', 'teh', 'Teh Melati', 4, 25000.00),
  ('2025-10-21', 'kopi', 'Kopi Gayo', 5, 55000.00),
  ('2025-11-12', 'teh', 'Teh Hijau', 3, 28000.00),
  ('2025-12-08', 'kopi', 'Kopi Toraja', 1, 62000.00),
  ('2026-01-09', 'kopi', 'Kopi Gayo', 6, 55000.00),
  ('2026-02-13', 'alat', 'Grinder Manual', 2, 350000.00),
  ('2026-03-18', 'teh', 'Teh Melati', 7, 25000.00),
  ('2026-04-15', 'kopi', 'Kopi Robusta', 4, 48000.00),
  ('2026-05-07', 'teh', 'Teh Hijau', 6, 28000.00),
  ('2026-06-19', 'alat', 'French Press', 1, 180000.00),
  ('2026-07-22', 'kopi', 'Kopi Gayo', 7, 55000.00),
  ('2026-08-11', 'teh', 'Teh Melati', 5, 25000.00);

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

Perbandingan Tahunan per Kategori #

Pertanyaan pertama: bagaimana penjualan tiap kategori tahun 2026 dibanding tahun 2025? Pola pengerjaannya — agregat dulu per kombinasi tahun-kategori, lalu join hasil agregat itu dengan dirinya sendiri untuk menyandingkan tahun:

sql
WITH bulanan AS (
  SELECT
    YEAR(tanggal) AS tahun,
    kategori,
    SUM(jumlah * harga) AS total
  FROM penjualan
  GROUP BY tahun, kategori
)
SELECT
  a.kategori,
  a.total AS total_2025,
  b.total AS total_2026,
  ROUND((b.total - a.total) / a.total * 100, 1) AS perubahan_persen
FROM bulanan a
JOIN bulanan b
  ON b.kategori = a.kategori AND b.tahun = 2026
WHERE a.tahun = 2025
ORDER BY a.kategori;

Output yang Dihasilkan:

text
+----------+------------+------------+------------------+
| kategori | total_2025 | total_2026 | perubahan_persen |
+----------+------------+------------+------------------+
| alat     |  710000.00 |  880000.00 |             23.9 |
| kopi     |  942000.00 |  907000.00 |             -3.7 |
| teh      |  474000.00 |  468000.00 |             -1.3 |
+----------+------------+------------+------------------+
3 rows in set (0.00 sec)

Mari kita bedah alurnya:

CTE bulanan merangkum penjualan per tahun per kategori menjadi enam baris: tiga kategori kali dua tahun. Kolom total memakai ekspresi jumlah * harga — nilai transaksi penuh.
Self join menyandingkan baris 2025 (a) dengan baris 2026 (b) dari kategori yang sama. Syarat join b.tahun = 2026 dan filter a.tahun = 2025 memastikan pasangan yang benar.
Kolom perubahan_persen menghitung selisih relatif dengan ROUND(..., 1) agar terbaca sebagai satu desimal. Angka positif berarti tumbuh.

Hasilnya siap dibaca langsung oleh manajer: kategori alat tumbuh 23,9 persen karena penjualan grinder dan french press tahun 2026 sudah melampaui total setahun 2025, sementara kopi dan teh justru terkoreksi tipis. Perhatikan pula bahwa laporan ini jujur terhadap datanya — tahun 2026 baru mencakup sampai Agustus, sehingga pertumbuhan alat yang terlihat “mengalahkan” setahun penuh 2025 merupakan sinyal yang layak ditelusuri lebih lanjut, bukan kesimpulan akhir.

Kontribusi Kumulatif Produk #

Laporan kedua menjawab: berapa persen kontribusi tiap produk terhadap total penjualan, dan bagaimana akumulatifnya bila produk diurutkan dari yang terbesar — pola Pareto yang klasik untuk melihat produk inti:

sql
WITH per_produk AS (
  SELECT
    produk,
    SUM(jumlah * harga) AS total
  FROM penjualan
  GROUP BY produk
),
dengan_total AS (
  SELECT
    produk,
    total,
    SUM(total) OVER () AS total_semua
  FROM per_produk
)
SELECT
  produk,
  total,
  ROUND(total / total_semua * 100, 1) AS kontribusi_persen,
  ROUND(SUM(total) OVER (ORDER BY total DESC) / total_semua * 100, 1) AS kumulatif_persen
FROM dengan_total
ORDER BY total DESC;

Output yang Dihasilkan:

text
+----------------+------------+-------------------+------------------+
| produk         | total      | kontribusi_persen | kumulatif_persen |
+----------------+------------+-------------------+------------------+
| Kopi Gayo      | 1375000.00 |              31.4 |             31.4 |
| Grinder Manual | 1050000.00 |              24.0 |             55.4 |
| Teh Melati     |  550000.00 |              12.6 |             67.9 |
| French Press   |  540000.00 |              12.3 |             80.2 |
| Teh Hijau      |  392000.00 |               8.9 |             89.2 |
| Kopi Robusta   |  288000.00 |               6.6 |             95.8 |
| Kopi Toraja    |  186000.00 |               4.2 |            100.0 |
+----------------+------------+-------------------+------------------+
7 rows in set (0.00 sec)

Tiga tahap CTE masing-masing punya tugas tunggal: per_produk mengagregasi, dengan_total menghitung keseluruhan sekali lewat SUM() OVER () tanpa partition, dan lapisan akhir menghitung dua persentase — kontribusi per produk serta akumulatif berjalan dengan SUM(total) OVER (ORDER BY total DESC). Pembacaan hasilnya lugas: dua produk teratas sudah menyumbang lebih dari separuh omzet, sebuah pola 80/20 versi mini yang menyarankan fokus stok pada sedikit produk inti.

Top-N Plus Kategori Lainnya #

Laporan ketiga menggabungkan window function dengan UNION untuk pola presentasi yang sering diminta: tampilkan tiga produk teratas apa adanya, lalu gumpalkan sisanya menjadi satu baris “Lainnya”:

sql
WITH per_produk AS (
  SELECT
    produk,
    SUM(jumlah * harga) AS total
  FROM penjualan
  GROUP BY produk
),
berperingkat AS (
  SELECT
    produk,
    total,
    ROW_NUMBER() OVER (ORDER BY total DESC) AS rn
  FROM per_produk
)
SELECT produk, total FROM berperingkat WHERE rn <= 3
UNION ALL
SELECT
  'Lainnya' AS produk,
  SUM(total) AS total
FROM berperingkat
WHERE rn > 3
ORDER BY total DESC;

Output yang Dihasilkan:

text
+----------------+------------+
| produk         | total      |
+----------------+------------+
| Lainnya        | 1406000.00 |
| Kopi Gayo      | 1375000.00 |
| Grinder Manual | 1050000.00 |
| Teh Melati     |  550000.00 |
+----------------+------------+
4 rows in set (0.00 sec)

Bagian atas query memfilter rn <= 3 untuk tiga produk teratas; bagian bawah mengagregasi baris rn > 3 menjadi satu baris Lainnya. UNION ALL dipilih karena kedua kumpulan dijamin tidak tumpang tindih — memakai UNION biasa hanya menambah biaya dedup yang tidak diperlukan. Label 'Lainnya' sengaja diurutkan mengikuti nilai totalnya, sehingga grafik batang dari laporan ini langsung masuk akal tanpa penyesuaian tambahan.

Analisis Kohort Sederhana #

Studi kasus penutup: kohort — kelompok pelanggan yang mulai pada periode yang sama, lalu kita amati perilaku kembalinya. Versi sederhananya memakai satu tabel transaksi dengan pelanggan dan bulan:

sql
CREATE TABLE transaksi_pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pelanggan VARCHAR(30) NOT NULL,
  tanggal DATE NOT NULL
);

INSERT INTO transaksi_pelanggan (pelanggan, tanggal) VALUES
  ('Budi', '2026-01-05'),
  ('Budi', '2026-01-28'),
  ('Budi', '2026-03-15'),
  ('Siti', '2026-01-12'),
  ('Siti', '2026-04-20'),
  ('Andi', '2026-02-02'),
  ('Andi', '2026-02-19'),
  ('Andi', '2026-05-11'),
  ('Andi', '2026-06-30'),
  ('Rina', '2026-03-09'),
  ('Dewi', '2026-02-14'),
  ('Dewi', '2026-03-22'),
  ('Dewi', '2026-06-05');

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

Kohort didefinisikan dari bulan transaksi pertama tiap pelanggan, lalu setiap transaksi berikutnya diukur jaraknya dalam bulan dari titik awal itu:

sql
WITH transaksi_bulanan AS (
  SELECT DISTINCT
    pelanggan,
    DATE_FORMAT(tanggal, '%Y-%m') AS bulan,
    YEAR(tanggal) * 12 + MONTH(tanggal) AS nomor_bulan
  FROM transaksi_pelanggan
),
awal AS (
  SELECT
    pelanggan,
    MIN(bulan) AS bulan_awal,
    MIN(nomor_bulan) AS nomor_bulan_awal
  FROM transaksi_bulanan
  GROUP BY pelanggan
),
per_jarak AS (
  SELECT
    a.bulan_awal,
    b.nomor_bulan - a.nomor_bulan_awal AS jarak_bulan
  FROM awal a
  JOIN transaksi_bulanan b ON b.pelanggan = a.pelanggan
)
SELECT
  bulan_awal AS kohort,
  COUNT(*) AS total_transaksi,
  COUNT(DISTINCT jarak_bulan) AS bulan_aktif_berbeda,
  MAX(jarak_bulan) AS bulan_terjauh
FROM per_jarak
GROUP BY bulan_awal
ORDER BY bulan_awal;

Output yang Dihasilkan:

text
+---------+-----------------+---------------------+---------------+
| kohort  | total_transaksi | bulan_aktif_berbeda | bulan_terjauh |
+---------+-----------------+---------------------+---------------+
| 2026-01 |               4 |                   3 |             3 |
| 2026-02 |               6 |                   4 |             4 |
| 2026-03 |               1 |                   1 |             0 |
+---------+-----------------+---------------------+---------------+
3 rows in set (0.00 sec)