Bagian 1: Persiapan Dataset

Setiap laporan analitik hanya sebaik data yang mendasarinya. Pada bagian ini kita membangun analitik_db untuk sebuah jaringan kedai kopi tiga cabang yang datanya diekspor dari aplikasi kasir — ekspor semacam itu hampir selalu membawa penumpang gelap: baris terkirim dua kali, nilai tidak wajar, dan teks yang rapi di mata tapi tidak identik di dalam basis data. Kita akan menyiapkan dua tabel — satu penampung data mentah, satu penampung hasil bersih — lalu memuat 28 baris data dua tahun dan menyelidiki cacatnya sebelum percaya pada satu angka pun.

Menganalisis Kebutuhan Database Analitik #

Kebutuhan analitik berbeda dari kebutuhan aplikasi transaksional. Aplikasi kasir menjaga setiap transaksi; database analitik justru boleh merangkum dan membuang. Dari sisi pembaca laporan, database ini harus mampu menjawab pertanyaan berikut:

berapa omzet tiap cabang, tiap bulan, dan tiap produk sepanjang 2025-2026;
produk mana yang menyumbang porsi terbesar dan bagaimana kontribusinya menumpuk;
bagaimana tren bulanan bergerak tanpa terkecoh lonjakan sesaat.

Untuk menjawabnya, data mentah tidak boleh langsung dianalisis. Kita pisahkan menjadi dua tabel dengan peran tegas:

Tabel	Peran	Karakter data
`penjualan_mentah`	staging: menampung ekspor apa adanya	boleh berduplikat dan bernoise
`penjualan_bersih`	hasil pembersihan untuk analisis	satu transaksi satu baris, dijaga `UNIQUE`

Pemisahan dua tabel ini meniru pola staging area pada pipeline data sungguhan: data kotor diisolasi agar bisa diaudit ulang kapan saja, sedangkan seluruh query analitik hanya boleh membaca tabel bersih. Bila kelak sebuah angka laporan dipertanyakan, kita selalu bisa melacak baris mentah asalnya.

flowchart LR A["Aplikasi kasir<br/>(ekspor kotor)"] --> B["penjualan_mentah"] B -->|"pembersihan<br/>Bagian 2"| C["penjualan_bersih"] C --> D["agregasi & laporan<br/>Bagian 2-4"]

Membangun Database dan Tabelnya #

Skema berikut adalah isi berkas database/skema.sql. Kedua tabel sengaja memakai kolom yang sama persis — tanggal, cabang, produk, qty, harga — karena tabel bersih kelak diisi lewat penyalinan dari tabel mentah. Perbedaannya ada pada satu constraint UNIQUE komposit di tabel 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)
);

Output yang Dihasilkan:

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

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mari kita bedah keputusan-keputusan pentingnya:

penjualan_mentah tanpa constraint kualitas — tabel staging tidak boleh menolak data; tugasnya justru merekam ekspor apa adanya, termasuk cacatnya. Constraint UNIQUE atau CHECK di sini justru membuat proses pemuatan gagal dan menyembunyikan masalah.
UNIQUE (tanggal, cabang, produk, qty, harga) di tabel bersih mendefinisikan arti “transaksi yang sama” menurut lima kolom data — id tidak ikut dihitung karena id hasil AUTO_INCREMENT berbeda pada baris duplikat. Constraint ini menjadi jaring pengaman: duplikat yang lolos dari proses pembersihan akan ditolak server.
DECIMAL(10, 2) untuk harga memakai tipe presisi tetap. FLOAT berisiko membulatkan harga sehingga total omzet bisa selisih beberapa rupiah dari penjumlahan manual.
Tanpa foreign key — skema analitik ini memang datar (dua tabel berdiri sendiri). Kolom cabang dan produk disimpan sebagai teks karena analisis kita berhenti di tingkat ringkasan, bukan referensial.

Verifikasi Struktur #

sql
SHOW TABLES;

Output yang Dihasilkan:

text
+-----------------------+
| Tables_in_analitik_db |
+-----------------------+
| penjualan_bersih      |
| penjualan_mentah      |
+-----------------------+
2 rows in set (0.00 sec)

sql
DESC penjualan_bersih;

Output yang Dihasilkan:

text
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| id      | int           | NO   | PRI | NULL    | auto_increment |
| tanggal | date          | NO   | MUL | NULL    |                |
| cabang  | varchar(20)   | NO   |     | NULL    |                |
| produk  | varchar(30)   | NO   |     | NULL    |                |
| qty     | int           | NO   |     | NULL    |                |
| harga   | decimal(10,2) | NO   |     | NULL    |                |
+---------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Perhatikan baris Key pada kolom tanggal: nilainya MUL, bukan UNI — tampilan yang mengecoh bila kita mengharapkan penanda unique. DESC membaca kolom tunggal, sedangkan constraint kita komposit: tanggal sendirian boleh berulang (banyak transaksi berbagi tanggal), yang unik adalah kombinasinya dengan empat kolom lain. Karena kolom pertama index komposit tidak menjamin keunikan sendirian, MySQL melaporkannya sebagai MUL — sinyal bahwa index multi-kolom menempel pada kolom tersebut. Penolakan duplikat tetap bekerja penuh atas gabungan lima kolom, dibuktikan lewat galat pada Bagian 2.

Memuat Data Mentah #

Sekarang kita muat isi berkas database/data-awal.sql — 28 baris penjualan dari Januari 2025 sampai Juni 2026. Perlakukan blok ini seolah datang dari sistem eksternal: kita sengaja tidak menghitung ulang isinya, dan kita belum tahu cacat apa yang dibawanya:

sql
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);

Output yang Dihasilkan:

text

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

Semua tanggal dan harga ditulis sebagai literal tetap — kebiasaan yang dipertahankan di seluruh proyek agar setiap angka bisa direproduksi persis. Delapan baris pertama terlihat normal saat disorot:

sql
SELECT id, tanggal, cabang, produk, qty, harga
FROM penjualan_mentah
ORDER BY id
LIMIT 8;

Output yang Dihasilkan:

text
+----+------------+----------+----------------+-----+-----------+
| id | tanggal    | cabang   | produk         | qty | harga     |
+----+------------+----------+----------------+-----+-----------+
|  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 |
+----+------------+----------+----------------+-----+-----------+
8 rows in set (0.00 sec)

Memeriksa Kualitas Data #

Pemeriksaan pertama membandingkan jumlah baris fisik dengan jumlah transaksi unik. COUNT(*) menghitung semua baris, sedangkan COUNT(DISTINCT ...) menghitung kombinasi nilai yang berbeda pada lima kolom data:

sql
SELECT
  COUNT(*) AS baris_mentah,
  COUNT(DISTINCT tanggal, cabang, produk, qty, harga) AS transaksi_unik
FROM penjualan_mentah;

Output yang Dihasilkan:

text
+--------------+----------------+
| baris_mentah | transaksi_unik |
+--------------+----------------+
|           28 |             25 |
+--------------+----------------+
1 row in set (0.00 sec)

Selisih 28 - 25 = 3 berarti ada 3 baris ganda — kembar persis pada kelima kolom. Untuk melihat baris mana yang berulang, kita kelompokkan berdasarkan seluruh kolom data lalu saring kelompok beranggota lebih dari satu:

sql
SELECT
  tanggal,
  cabang,
  produk,
  qty,
  harga,
  COUNT(*) AS kemunculan
FROM penjualan_mentah
GROUP BY tanggal, cabang, produk, qty, harga
HAVING COUNT(*) > 1
ORDER BY tanggal, cabang, produk;

Output yang Dihasilkan:

text
+------------+----------+-------------+-----+----------+------------+
| tanggal    | cabang   | produk      | qty | harga    | kemunculan |
+------------+----------+-------------+-----+----------+------------+
| 2025-01-22 | Bandung  | Teh Melati  |   6 | 25000.00 |          2 |
| 2025-02-08 | Surabaya | Kopi Toraja |   2 | 62000.00 |          2 |
| 2026-01-09 | Jakarta  | Kopi Gayo   |   3 | 55000.00 |          2 |
+------------+----------+-------------+-----+----------+------------+
3 rows in set (0.00 sec)

Cara ketiga memakai ROW_NUMBER() yang mempartisi baris menurut kelima kolom data: setiap anggota kembar mendapat nomor urut, dan hanya kemunculan kedua ke atas yang kita tampilkan. Teknik ini kelak menjadi dasar pembersihan yang lebih terkontrol:

sql
WITH bertanda AS (
  SELECT
    id,
    tanggal,
    cabang,
    produk,
    qty,
    harga,
    ROW_NUMBER() OVER (
      PARTITION BY tanggal, cabang, produk, qty, harga
      ORDER BY id
    ) AS kemunculan_ke
  FROM penjualan_mentah
)
SELECT id, tanggal, cabang, produk, qty, harga, kemunculan_ke
FROM bertanda
WHERE kemunculan_ke > 1
ORDER BY id;

Output yang Dihasilkan:

text
+----+------------+----------+-------------+-----+----------+---------------+
| id | tanggal    | cabang   | produk      | qty | harga    | kemunculan_ke |
+----+------------+----------+-------------+-----+----------+---------------+
| 25 | 2025-01-22 | Bandung  | Teh Melati  |   6 | 25000.00 |             2 |
| 26 | 2026-01-09 | Jakarta  | Kopi Gayo   |   3 | 55000.00 |             2 |
| 27 | 2025-02-08 | Surabaya | Kopi Toraja |   2 | 62000.00 |             2 |
+----+------------+----------+-------------+-----+----------+---------------+
3 rows in set (0.00 sec)

Cacat berikutnya tidak berupa duplikat. Sensus cabang memperlihatkan empat nilai padahal jaringan hanya punya tiga cabang:

sql
SELECT cabang, COUNT(*) AS baris
FROM penjualan_mentah
GROUP BY cabang
ORDER BY cabang;

Output yang Dihasilkan:

text
+----------+-------+
| cabang   | baris |
+----------+-------+
| Bandung  |     8 |
| Bandung  |     1 |
| Jakarta  |    10 |
| Surabaya |     9 |
+----------+-------+
4 rows in set (0.00 sec)

Dua baris Bandung di hasil di atas sebenarnya dua nilai berbeda: salah satunya menyimpan spasi di ujung. Tabel ASCII tidak menampilkan spasi buntut, jadi kita buktikan dengan CHAR_LENGTH:

sql
SELECT id, cabang, CHAR_LENGTH(cabang) AS panjang
FROM penjualan_mentah
WHERE cabang <> TRIM(cabang);

Output yang Dihasilkan:

text
+----+----------+---------+
| id | cabang   | panjang |
+----+----------+---------+
| 23 | Bandung  |       8 |
+----+----------+---------+
1 row in set (0.00 sec)

Terakhir, nilai yang tidak wajar: transaksi dengan qty nol tidak menjual apa pun dan akan menggelembungkan jumlah transaksi tanpa menambah omzet:

sql
SELECT id, tanggal, cabang, produk, qty, harga
FROM penjualan_mentah
WHERE qty = 0;

Output yang Dihasilkan:

text
+----+------------+---------+-----------+-----+----------+
| id | tanggal    | cabang  | produk    | qty | harga    |
+----+------------+---------+-----------+-----+----------+
| 28 | 2026-03-30 | Jakarta | Teh Hijau |   0 | 28000.00 |
+----+------------+---------+-----------+-----+----------+
1 row in set (0.00 sec)

Temuan bagian ini menjadi daftar periksa untuk Bagian 2: tiga baris kembar persis, satu nilai cabang berspasi buntut, dan satu transaksi qty nol. Tiga jenis cacat itu akan dibersihkan sekaligus dalam satu transaksi INSERT ... SELECT DISTINCT — lalu omzet mulai dihitung.

Membersihkan Database Latihan #

Bagian ini selesai dan analitik_db boleh kita bongkar agar bagian berikutnya mulai dari kondisi bersih:

sql
DROP DATABASE IF EXISTS analitik_db;

Output yang Dihasilkan:

text

Query OK, 2 rows affected (0.00 sec)

Perintah DROP DATABASE menghapus database beserta seluruh tabel di dalamnya tanpa konfirmasi. Karena setiap bagian proyek ini membuka bloknya dengan pembangunan ulang dari nol, kebiasaan membersihkan di akhir tidak pernah membuat kita kehilangan apa pun.

Referensi #

Data mentah sudah termuat dan daftar cacatnya lengkap. Bagian 2 memasuki dapur pembersihan: menyalin 28 baris mentah menjadi 24 baris bersih lewat satu transaksi, lalu mengeluarkan agregat pertama — omzet per cabang, per bulan, dan per produk.

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- =============================================================
-- 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);

sql
-- =============================================================
-- 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)
);

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;

sql
-- =============================================================
-- 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;