Bagian 2: Agregasi dan Grouping

-- =============================================================
-- Nama berkas : data-awal.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Memuat 28 baris data penjualan mentah Januari-Juni
--               dua tahun (2025 dan 2026) — sengaja berisi cacat:
--               3 baris duplikat persis (id 25-27), 1 baris qty nol
--               (id 28), dan 1 cabang dengan spasi buntut 'Bandung '
--               (id 23). Perlakukan seolah ekspor dari sistem luar.
-- Cara pakai  : mysql -u root -p analitik_db < database/data-awal.sql
--               (jalankan setelah database/skema.sql)
-- =============================================================

USE analitik_db;

INSERT INTO penjualan_mentah (id, tanggal, cabang, produk, qty, harga) VALUES
  (1,  '2025-01-10', 'Jakarta',  'Kopi Gayo',      4, 55000.00),
  (2,  '2025-01-22', 'Bandung',  'Teh Melati',     6, 25000.00),
  (3,  '2025-02-08', 'Surabaya', 'Kopi Toraja',    2, 62000.00),
  (4,  '2025-02-19', 'Jakarta',  'Teh Hijau',      5, 28000.00),
  (5,  '2025-03-05', 'Bandung',  'Kopi Gayo',      2, 55000.00),
  (6,  '2025-03-17', 'Surabaya', 'Teh Melati',     4, 25000.00),
  (7,  '2025-04-09', 'Jakarta',  'Kopi Toraja',    2, 62000.00),
  (8,  '2025-04-21', 'Bandung',  'Grinder Manual', 1, 350000.00),
  (9,  '2025-05-06', 'Surabaya', 'Kopi Gayo',      2, 55000.00),
  (10, '2025-05-18', 'Jakarta',  'Teh Melati',     6, 25000.00),
  (11, '2025-06-03', 'Bandung',  'Kopi Toraja',    2, 62000.00),
  (12, '2025-06-15', 'Surabaya', 'Teh Hijau',      5, 28000.00),
  (13, '2026-01-09', 'Jakarta',  'Kopi Gayo',      3, 55000.00),
  (14, '2026-01-21', 'Bandung',  'Teh Melati',     7, 25000.00),
  (15, '2026-02-05', 'Surabaya', 'Kopi Toraja',    2, 62000.00),
  (16, '2026-02-17', 'Jakarta',  'Teh Hijau',      6, 28000.00),
  (17, '2026-03-04', 'Bandung',  'Kopi Gayo',      2, 55000.00),
  (18, '2026-03-16', 'Surabaya', 'Teh Melati',     3, 25000.00),
  (19, '2026-04-08', 'Jakarta',  'Kopi Toraja',    3, 62000.00),
  (20, '2026-04-20', 'Bandung',  'Grinder Manual', 2, 350000.00),
  (21, '2026-05-05', 'Surabaya', 'Kopi Gayo',      2, 55000.00),
  (22, '2026-05-17', 'Jakarta',  'Teh Melati',     7, 25000.00),
  (23, '2026-06-02', 'Bandung ', 'Kopi Toraja',    2, 62000.00),
  (24, '2026-06-14', 'Surabaya', 'Teh Hijau',      4, 28000.00),
  (25, '2025-01-22', 'Bandung',  'Teh Melati',     6, 25000.00),
  (26, '2026-01-09', 'Jakarta',  'Kopi Gayo',      3, 55000.00),
  (27, '2025-02-08', 'Surabaya', 'Kopi Toraja',    2, 62000.00),
  (28, '2026-03-30', 'Jakarta',  'Teh Hijau',      0, 28000.00);

-- =============================================================
-- Nama berkas : skema.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Membangun ulang database analitik_db dengan dua
--               tabel — penjualan_mentah (staging data kotor,
--               tanpa constraint kualitas) dan penjualan_bersih
--               (hasil pembersihan, dijaga UNIQUE komposit lima
--               kolom agar baris duplikat tidak bisa masuk).
-- Cara pakai  : mysql -u root -p < database/skema.sql
--               (lanjutkan dengan database/data-awal.sql dan
--               queries/staging-bersih.sql)
-- =============================================================

DROP DATABASE IF EXISTS analitik_db;
CREATE DATABASE analitik_db;
USE analitik_db;

CREATE TABLE penjualan_mentah (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tanggal DATE NOT NULL,
  cabang VARCHAR(20) NOT NULL,
  produk VARCHAR(30) NOT NULL,
  qty INT NOT NULL,
  harga DECIMAL(10, 2) NOT NULL
);

CREATE TABLE penjualan_bersih (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tanggal DATE NOT NULL,
  cabang VARCHAR(20) NOT NULL,
  produk VARCHAR(30) NOT NULL,
  qty INT NOT NULL,
  harga DECIMAL(10, 2) NOT NULL,
  CONSTRAINT uq_bersih_transaksi
    UNIQUE (tanggal, cabang, produk, qty, harga)
);

-- =============================================================
-- Nama berkas : kontribusi-kumulatif.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Menghitung kontribusi omzet tiap produk terhadap
--               total beserta akumulatifnya (pola Pareto) memakai
--               dua window function berjenjang.
-- Cara pakai  : mysql -u root -p analitik_db < queries/kontribusi-kumulatif.sql
-- =============================================================

USE analitik_db;

WITH per_produk AS (
  SELECT
    produk,
    SUM(qty * harga) AS omzet
  FROM penjualan_bersih
  GROUP BY produk
)
SELECT
  produk,
  omzet,
  ROUND(omzet / SUM(omzet) OVER () * 100, 1) AS kontribusi_persen,
  ROUND(
    SUM(omzet) OVER (
      ORDER BY omzet DESC, produk
      ROWS UNBOUNDED PRECEDING
    ) / SUM(omzet) OVER () * 100, 1
  ) AS kumulatif_persen
FROM per_produk
ORDER BY omzet DESC, produk;

-- =============================================================
-- Nama berkas : laporan-yoy.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Membandingkan omzet tiap cabang Januari-Juni 2026
--               terhadap periode sama tahun 2025 (YoY) lewat CTE
--               yang di-self-join.
-- Cara pakai  : mysql -u root -p analitik_db < queries/laporan-yoy.sql
-- =============================================================

USE analitik_db;

WITH omzet_cabang AS (
  SELECT
    YEAR(tanggal) AS tahun,
    cabang,
    SUM(qty * harga) AS omzet
  FROM penjualan_bersih
  GROUP BY tahun, cabang
)
SELECT
  a.cabang,
  a.omzet AS omzet_2025,
  b.omzet AS omzet_2026,
  ROUND((b.omzet - a.omzet) / a.omzet * 100, 1) AS perubahan_persen
FROM omzet_cabang a
JOIN omzet_cabang b
  ON b.cabang = a.cabang AND b.tahun = 2026
WHERE a.tahun = 2025
ORDER BY perubahan_persen DESC, a.cabang;

-- =============================================================
-- Nama berkas : moving-average.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Menghitung rata-rata bergerak (moving average) omzet
--               3 bulan dengan AVG() OVER dan frame ROWS.
-- Cara pakai  : mysql -u root -p analitik_db < queries/moving-average.sql
-- =============================================================

USE analitik_db;

WITH omzet_bulanan AS (
  SELECT
    DATE_FORMAT(tanggal, '%Y-%m') AS bulan,
    SUM(qty * harga) AS omzet
  FROM penjualan_bersih
  GROUP BY bulan
)
SELECT
  bulan,
  omzet,
  ROUND(
    AVG(omzet) OVER (
      ORDER BY bulan
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2
  ) AS rata3_bulan
FROM omzet_bulanan
ORDER BY bulan;

-- =============================================================
-- Nama berkas : omzet-bulanan.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Merangkum omzet per bulan (YYYY-MM) dari data
--               bersih, terurut naik.
-- Cara pakai  : mysql -u root -p analitik_db < queries/omzet-bulanan.sql
-- =============================================================

USE analitik_db;

SELECT
  DATE_FORMAT(tanggal, '%Y-%m') AS bulan,
  COUNT(*) AS jumlah_transaksi,
  SUM(qty * harga) AS omzet
FROM penjualan_bersih
GROUP BY bulan
ORDER BY bulan;

-- =============================================================
-- Nama berkas : omzet-per-cabang.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Menghitung total omzet (jumlah x harga) tiap cabang
--               dari data bersih, lengkap jumlah transaksinya.
-- Cara pakai  : mysql -u root -p analitik_db < queries/omzet-per-cabang.sql
-- =============================================================

USE analitik_db;

SELECT
  cabang,
  COUNT(*) AS jumlah_transaksi,
  SUM(qty * harga) AS omzet
FROM penjualan_bersih
GROUP BY cabang
ORDER BY omzet DESC, cabang;

-- =============================================================
-- Nama berkas : omzet-per-produk.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Menghitung omzet dan total unit tiap produk dari
--               data bersih, terurut dari omzet terbesar.
-- Cara pakai  : mysql -u root -p analitik_db < queries/omzet-per-produk.sql
-- =============================================================

USE analitik_db;

SELECT
  produk,
  SUM(qty) AS total_unit,
  SUM(qty * harga) AS omzet
FROM penjualan_bersih
GROUP BY produk
ORDER BY omzet DESC, produk;

-- =============================================================
-- Nama berkas : peringkat-produk.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Memberi peringkat produk berdasarkan omzet di dalam
--               masing-masing cabang dengan CTE agregat + RANK()
--               OVER (PARTITION BY cabang ORDER BY omzet DESC).
-- Cara pakai  : mysql -u root -p analitik_db < queries/peringkat-produk.sql
--               (jalankan setelah staging; tambahkan filter
--               peringkat <= 2 untuk dua produk teratas per cabang)
-- =============================================================

USE analitik_db;

WITH omzet_cabang_produk AS (
  SELECT
    cabang,
    produk,
    SUM(qty * harga) AS omzet
  FROM penjualan_bersih
  GROUP BY cabang, produk
)
SELECT
  cabang,
  produk,
  omzet,
  RANK() OVER (
    PARTITION BY cabang
    ORDER BY omzet DESC
  ) AS peringkat
FROM omzet_cabang_produk
ORDER BY cabang, peringkat;

-- =============================================================
-- Nama berkas : running-total.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Menghitung omzet berjalan (running total) per bulan
--               dengan CTE agregat + SUM() OVER ber-frame ROWS
--               UNBOUNDED PRECEDING.
-- Cara pakai  : mysql -u root -p analitik_db < queries/running-total.sql
--               (jalankan setelah staging)
-- =============================================================

USE analitik_db;

WITH omzet_bulanan AS (
  SELECT
    DATE_FORMAT(tanggal, '%Y-%m') AS bulan,
    SUM(qty * harga) AS omzet
  FROM penjualan_bersih
  GROUP BY bulan
)
SELECT
  bulan,
  omzet,
  SUM(omzet) OVER (
    ORDER BY bulan
    ROWS UNBOUNDED PRECEDING
  ) AS omzet_berjalan
FROM omzet_bulanan
ORDER BY bulan;

-- =============================================================
-- Nama berkas : staging-bersih.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Proses staging — menyalin data dari penjualan_mentah
--               ke penjualan_bersih sekaligus membersihkannya dalam
--               satu transaksi: buang qty nol (WHERE qty > 0),
--               buang duplikat persis (SELECT DISTINCT), dan rapikan
--               spasi buntut cabang lewat TRIM.
-- Cara pakai  : mysql -u root -p analitik_db < queries/staging-bersih.sql
--               (jalankan setelah database/skema.sql dan
--               database/data-awal.sql)
-- =============================================================

USE analitik_db;

START TRANSACTION;

INSERT INTO penjualan_bersih (tanggal, cabang, produk, qty, harga)
SELECT DISTINCT
  tanggal,
  TRIM(cabang) AS cabang,
  produk,
  qty,
  harga
FROM penjualan_mentah
WHERE qty > 0;

COMMIT;

-- =============================================================
-- Nama berkas : top-n-lainnya.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Menampilkan 3 produk beromzet terbesar apa adanya
--               lalu menggumpalkan sisanya menjadi baris Lainnya
--               lewat ROW_NUMBER + UNION ALL. Kolom posisi tetap
--               (nomor_urut 4) menjaga baris Lainnya di urutan
--               terakhir laporan.
-- Cara pakai  : mysql -u root -p analitik_db < queries/top-n-lainnya.sql
--               (jalankan setelah staging)
-- =============================================================

USE analitik_db;

WITH per_produk AS (
  SELECT
    produk,
    SUM(qty * harga) AS omzet
  FROM penjualan_bersih
  GROUP BY produk
),
berperingkat AS (
  SELECT
    produk,
    omzet,
    ROW_NUMBER() OVER (ORDER BY omzet DESC, produk) AS nomor_urut
  FROM per_produk
)
SELECT produk, omzet
FROM (
  SELECT nomor_urut, produk, omzet
  FROM berperingkat
  WHERE nomor_urut <= 3
  UNION ALL
  SELECT 4, 'Lainnya', SUM(omzet)
  FROM berperingkat
  WHERE nomor_urut > 3
) AS ringkas
ORDER BY nomor_urut;

-- =============================================================
-- Nama berkas : pemeriksaan.sql
-- Proyek      : Database Analisis Penjualan (Course MySQL)
-- Tujuan      : Uji akhir otomatis — memeriksa hasil staging
--               (baris mentah vs bersih, cabang rapi), omzet per
--               tahun, produk teratas, dan kelengkapan bulan.
--               Ekspektasi tiap query dituliskan sebagai komentar.
-- Cara pakai  : mysql -h 127.0.0.1 -P 3306 -u root < tests/pemeriksaan.sql
--               (jalankan setelah database/skema.sql, data-awal.sql,
--               dan queries/staging-bersih.sql)
-- =============================================================

USE analitik_db;

-- Pemeriksaan 1: data mentah termuat lengkap.
-- Ekspektasi: baris_mentah = 28.
SELECT COUNT(*) AS baris_mentah FROM penjualan_mentah;

-- Pemeriksaan 2: staging membuang 3 duplikat dan 1 baris qty nol.
-- Ekspektasi: baris_bersih = 24.
SELECT COUNT(*) AS baris_bersih FROM penjualan_bersih;

-- Pemeriksaan 3: spasi buntut cabang hilang — hanya 3 cabang tersisa.
-- Ekspektasi: 3 baris — Bandung 8, Jakarta 8, Surabaya 8.
SELECT cabang, COUNT(*) AS baris
FROM penjualan_bersih
GROUP BY cabang
ORDER BY cabang;

-- Pemeriksaan 4: omzet per tahun (Januari-Juni 2025 dan 2026).
-- Ekspektasi: 2 baris — 2025 sebesar 1842000.00, 2026 sebesar 2224000.00.
SELECT
  YEAR(tanggal) AS tahun,
  SUM(qty * harga) AS omzet
FROM penjualan_bersih
GROUP BY tahun
ORDER BY tahun;

-- Pemeriksaan 5: produk beromzet terbesar.
-- Ekspektasi: 1 baris — Grinder Manual dengan omzet 1050000.00.
SELECT
  produk,
  SUM(qty * harga) AS omzet
FROM penjualan_bersih
GROUP BY produk
ORDER BY omzet DESC, produk
LIMIT 1;

-- Pemeriksaan 6: dua produk seri di peringkat kedua.
-- Ekspektasi: 2 baris — Kopi Gayo dan Teh Melati, keduanya 825000.00.
SELECT
  produk,
  SUM(qty * harga) AS omzet
FROM penjualan_bersih
GROUP BY produk
HAVING SUM(qty * harga) = 825000.00
ORDER BY produk;

-- Pemeriksaan 7: setiap bulan Januari-Juni pada kedua tahun berisi data.
-- Ekspektasi: jumlah_bulan = 12.
SELECT COUNT(*) AS jumlah_bulan
FROM (
  SELECT DATE_FORMAT(tanggal, '%Y-%m') AS bulan
  FROM penjualan_bersih
  GROUP BY bulan
) AS per_bulan;