Proyek: Database Analisis Penjualan

Proyek sebelumnya bekerja dengan data yang sudah rapi sejak awal; proyek Database Analisis Penjualan memulai dari sisi yang berlawanan. Data penjualan di dunia nyata jarang datang bersih — baris terkirim dua kali oleh aplikasi kasir, nama cabang menyimpan spasi tak terlihat, dan nilai tidak wajar menyusup di tengah transaksi sah. Tugas analis bukan sekadar menghitung angka, melainkan memastikan angka itu layak dihitung lebih dulu. Proyek ini melatih alur kerja tersebut secara utuh pada database analitik_db: data mentah masuk ke tabel staging, dibersihkan ke tabel bersih, lalu dianalisis sampai ke laporan yang bisa dibaca manajer.

Alur proyek mengikuti pipeline yang lazim dipakai di dunia data: raw → staging → agregat → analitik. Setiap bagian membangun satu lapisan pipeline, dan setiap bagian juga mandiri — blok pertamanya selalu membangun ulang analitik_db dari nol — sehingga kita bisa masuk dari bagian mana pun tanpa kehilangan kondisi data. Seluruh berkas SQL proyek hidup di bundle halaman ini pada bagian Berkas Sumber dan dapat diunduh serta dijalankan ulang kapan saja.

Tujuan Proyek #

Saat proyek selesai, kita memiliki database analitik yang mampu menjawab pertanyaan-pertanyaan berikut:

mendeteksi cacat data penjualan mentah dengan COUNT, COUNT DISTINCT, GROUP BY, dan HAVING;
membersihkan data lewat INSERT ... SELECT DISTINCT yang dibungkus satu transaksi, dengan kolom UNIQUE sebagai jaring pengaman;
merangkum omzet per cabang, per bulan, dan per produk dengan GROUP BY;
memberi peringkat produk per cabang serta menghitung running total dan moving average omzet dengan window function;
menghasilkan laporan analitis: perbandingan tahun ke tahun, kontribusi kumulatif produk, dan ringkasan top-3 plus “Lainnya”.

Prasyarat #

Proyek ini memakai materi dari beberapa section sebelumnya. Sebelum mulai, pastikan kita sudah nyaman dengan hal-hal berikut:

CREATE TABLE dengan kolom UNIQUE dari section Relational Database Design;
INSERT, SELECT DISTINCT, dan fungsi string TRIM dari section Dasar SQL dan Fungsi SQL;
GROUP BY, HAVING, dan agregat SUM, COUNT, AVG dari section Agregasi Data;
WITH (CTE) dan UNION ALL dari section Set Operations dan CTE;
window function RANK, SUM() OVER, dan AVG() OVER dari section Window Functions;
START TRANSACTION, COMMIT, dan ROLLBACK dari section Transactions.

Peta Bagian #

Bagian	Fokus	Hasil akhir
1	Persiapan dataset	Tabel `penjualan_mentah` dan `penjualan_bersih`, data mentah 28 baris, daftar cacat data
2	Agregasi dan grouping	Proses staging dalam satu transaksi, omzet per cabang, bulan, dan produk
3	Window functions	Peringkat produk per cabang, running total, dan moving average dengan CTE beruntun
4	Laporan analitis	Laporan YoY, kontribusi kumulatif, top-3 plus Lainnya, uji akhir otomatis

Struktur Berkas Proyek #

Berkas pendukung proyek mengikuti struktur folder berikut:

text
database-analisis-penjualan/
├── database/
│   ├── skema.sql         (DROP + CREATE DATABASE + dua CREATE TABLE)
│   └── data-awal.sql     (28 baris penjualan mentah 2025-2026, sengaja berkarakat)
├── queries/
│   ├── staging-bersih.sql
│   ├── omzet-per-cabang.sql
│   ├── omzet-bulanan.sql
│   ├── omzet-per-produk.sql
│   ├── peringkat-produk.sql
│   ├── running-total.sql
│   ├── moving-average.sql
│   ├── laporan-yoy.sql
│   ├── kontribusi-kumulatif.sql
│   └── top-n-lainnya.sql
└── tests/
    └── pemeriksaan.sql   (uji akhir dengan ekspektasi tertulis)

Berkas database/skema.sql membangun ulang database dari nol, database/data-awal.sql memuat data mentah yang sengaja menyimpan cacat, queries/staging-bersih.sql menjalankan pembersihan, sembilan berkas laporan lainnya siap pakai, dan tests/pemeriksaan.sql memeriksa seluruh hasil akhir proyek secara otomatis. Alur data antar-tabelnya dirangkum diagram berikut:

flowchart LR A["Sumber eksternal (28 baris mentah)"] --> B["penjualan_mentah data kotor"] B -->|"INSERT DISTINCT + TRIM + filter"| C["penjualan_bersih 24 baris"] C --> D["Agregasi GROUP BY cabang / bulan / produk"] C --> E["Window function RANK / running total / MA"] D --> F["Laporan analitis YoY / Pareto / top-3"] E --> F

Diagram ini juga menjadi peta jalan empat bagian proyek: Bagian 1 membangun dua tabel pertama, Bagian 2 menyalin dan membersihkan datanya, Bagian 3 membaca tren lewat window function, dan Bagian 4 merangkai semuanya menjadi laporan. Mari kita mulai dari Bagian 1: membangun rumah untuk data yang belum bisa dipercaya.

# Bagian 1: Persiapan Dataset

Bangun database analitik_db dengan tabel mentah dan tabel bersih, muat 28 baris data penjualan dua tahun, lalu selidiki kualitasnya dengan COUNT DISTINCT, GROUP BY, dan ROW_NUMBER.

Buka → # Bagian 2: Agregasi dan Grouping

Bersihkan 28 baris data mentah menjadi 24 baris bersih lewat INSERT SELECT DISTINCT dalam satu transaksi, verifikasi hasil staging dengan penolakan UNIQUE, lalu rangkum omzet per cabang, per bulan, dan per produk.

Buka → # Bagian 3: Window Functions

Angkat analisis ke lapisan berikutnya dengan CTE beruntun dan window function: peringkat produk per cabang dengan RANK, running total omzet per bulan, dan moving average tiga bulan.

Buka → # Bagian 4: Laporan Analitis

Tutup proyek dengan laporan analitis: perbandingan YoY lewat self-join agregat, kontribusi kumulatif produk ala Pareto, ringkasan top-3 plus Lainnya dengan UNION ALL, dan uji akhir otomatis dari kondisi bersih.

Buka →

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;