Bagian 4: Analisis Penjualan

Data transaksi yang tersimpan rapi hanya setengah dari nilai database; setengah lainnya baru muncul saat data itu diolah menjadi keputusan. Produk mana yang perlu dibeli ulang dalam jumlah lebih besar, pelanggan mana yang layak dapat bonus loyalitas, pasangan produk mana yang sebaiknya diletakkan berdampingan di rak, dan berapa sebenarnya laba kotor tiap produk setelah harga promo diperhitungkan? Pada bagian penutup ini kita memuat riwayat penjualan satu kuartal penuh, lalu mengajunya dengan lima query analisis: omzet bulanan, peringkat produk, pelanggan terbaik, analisis keranjang, dan margin laba kotor.

Data Satu Kuartal #

Berkas pembuka membangun ulang skema dan mengisi sejarah toko sepanjang kuartal I 2026: dua belas pesanan Januari sampai Maret dengan dua puluh sembilan baris item, semuanya berstatus selesai. Stok yang dimuat adalah stok akhir kuartal ditambah total terjual, sehingga setelah pengurangan stok di akhir berkas, papan stok kembali ke angka-angka kondisi awal toko dari Bagian 3 — konsistensi yang sengaja dirancang agar antarbagian saling bisa diverifikasi:

sql
siapkan.sql
-- siapkan.sql — fondasi Bagian 4: skema dari Bagian 1 + data master
-- + dua belas pesanan lengkap sepanjang kuartal I 2026 (29 item).
-- Bagian ini dimaksudkan dapat dijalankan sendiri dari database kosong.

CREATE TABLE kategori (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text   NOT NULL UNIQUE
);

CREATE TABLE produk (
    id          integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kode        text           NOT NULL UNIQUE,
    nama        text           NOT NULL,
    kategori_id integer        NOT NULL REFERENCES kategori (id),
    harga_beli  numeric(12, 2) NOT NULL CHECK (harga_beli > 0),
    harga_jual  numeric(12, 2) NOT NULL CHECK (harga_jual > 0),
    stok        integer        NOT NULL CHECK (stok >= 0)
);

CREATE TABLE pelanggan (
    id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama  text   NOT NULL,
    email text   NOT NULL UNIQUE,
    kota  text   NOT NULL
);

CREATE TABLE pesanan (
    id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pelanggan_id integer NOT NULL REFERENCES pelanggan (id),
    tanggal      date    NOT NULL,
    status       text    NOT NULL DEFAULT 'baru'
                 CHECK (status IN ('baru', 'diproses', 'selesai', 'batal'))
);

CREATE TABLE pesanan_item (
    id                   integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pesanan_id           integer        NOT NULL REFERENCES pesanan (id) ON DELETE CASCADE,
    produk_id            integer        NOT NULL REFERENCES produk (id),
    qty                  integer        NOT NULL CHECK (qty > 0),
    harga_saat_transaksi numeric(12, 2) NOT NULL,
    subtotal             numeric(12, 2) GENERATED ALWAYS AS (qty * harga_saat_transaksi) STORED
);

CREATE INDEX idx_pesanan_tanggal       ON pesanan (tanggal);
CREATE INDEX idx_pesanan_pelanggan     ON pesanan (pelanggan_id);
CREATE INDEX idx_pesanan_item_pesanan  ON pesanan_item (pesanan_id);
CREATE INDEX idx_pesanan_item_produk   ON pesanan_item (produk_id);


INSERT INTO kategori (nama) VALUES
    ('Sembako'),
    ('Minuman'),
    ('Makanan'),
    ('Kebersihan');

-- stok awal kuartal: jumlah akhir + total terjual sepanjang kuartal
INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok) VALUES
    ('PRD-001', 'Beras Premium 5kg',        1, 61000.00, 68000.00,  48),
    ('PRD-002', 'Gula Pasir 1kg',           1, 15000.00, 17000.00,  80),
    ('PRD-003', 'Minyak Goreng 2L',         1, 32000.00, 36500.00,  42),
    ('PRD-004', 'Telur Ayam 1kg',           1, 27000.00, 31000.00,  34),
    ('PRD-005', 'Kopi Sachet Instan',       2,  9000.00, 12000.00, 126),
    ('PRD-006', 'Teh Celup 50 kantong',     2, 11000.00, 13500.00,  47),
    ('PRD-007', 'Air Mineral 600ml',        2,  2500.00,  4000.00, 246),
    ('PRD-008', 'Mie Instan Goreng',        3,  2800.00,  3500.00, 170),
    ('PRD-009', 'Biskuit Cokelat',          3,  7500.00,  9500.00,  35),
    ('PRD-010', 'Sabun Cuci Piring 780ml',  4, 16000.00, 19500.00,  27);

INSERT INTO pelanggan (nama, email, kota) VALUES
    ('Budi Santoso', '[email protected]',  'Bandung'),
    ('Siti Rahayu',  '[email protected]',  'Bandung'),
    ('Andi Wijaya',  '[email protected]',  'Jakarta'),
    ('Dewi Lestari', '[email protected]',  'Surabaya'),
    ('Rudi Hartono', '[email protected]',  'Bandung'),
    ('Maya Putri',   '[email protected]',  'Jakarta');

-- dua belas pesanan sepanjang Januari-Maret 2026, semuanya selesai.
-- Pesanan 7 memuat harga promo Kopi 11500 (snapshot harga saat transaksi).
BEGIN;

INSERT INTO pesanan (pelanggan_id, tanggal, status) VALUES
    (2, DATE '2026-01-05', 'selesai'),
    (1, DATE '2026-01-12', 'selesai'),
    (3, DATE '2026-01-25', 'selesai'),
    (2, DATE '2026-02-03', 'selesai'),
    (4, DATE '2026-02-08', 'selesai'),
    (1, DATE '2026-02-15', 'selesai'),
    (5, DATE '2026-02-21', 'selesai'),
    (2, DATE '2026-03-02', 'selesai'),
    (6, DATE '2026-03-09', 'selesai'),
    (3, DATE '2026-03-16', 'selesai'),
    (4, DATE '2026-03-23', 'selesai'),
    (1, DATE '2026-03-30', 'selesai');

INSERT INTO pesanan_item (pesanan_id, produk_id, qty, harga_saat_transaksi)
SELECT p.id, pr.id, v.qty, v.harga
FROM (VALUES
    ('2026-01-05', 2, 'PRD-005', 10, 12000.00),
    ('2026-01-05', 2, 'PRD-002',  5, 17000.00),
    ('2026-01-12', 1, 'PRD-001',  2, 68000.00),
    ('2026-01-12', 1, 'PRD-003',  3, 36500.00),
    ('2026-01-25', 3, 'PRD-008', 20,  3500.00),
    ('2026-01-25', 3, 'PRD-007', 24,  4000.00),
    ('2026-02-03', 2, 'PRD-005', 12, 12000.00),
    ('2026-02-03', 2, 'PRD-002',  4, 17000.00),
    ('2026-02-03', 2, 'PRD-006',  2, 13500.00),
    ('2026-02-08', 4, 'PRD-010',  3, 19500.00),
    ('2026-02-08', 4, 'PRD-007', 12,  4000.00),
    ('2026-02-15', 1, 'PRD-001',  1, 68000.00),
    ('2026-02-15', 1, 'PRD-004',  5, 31000.00),
    ('2026-02-15', 1, 'PRD-008', 10,  3500.00),
    ('2026-02-21', 5, 'PRD-005',  8, 11500.00),
    ('2026-02-21', 5, 'PRD-007', 30,  4000.00),
    ('2026-03-02', 2, 'PRD-008', 25,  3500.00),
    ('2026-03-02', 2, 'PRD-007', 30,  4000.00),
    ('2026-03-09', 6, 'PRD-001',  3, 68000.00),
    ('2026-03-09', 6, 'PRD-002',  6, 17000.00),
    ('2026-03-09', 6, 'PRD-005',  6, 12000.00),
    ('2026-03-16', 3, 'PRD-008', 15,  3500.00),
    ('2026-03-16', 3, 'PRD-009',  5,  9500.00),
    ('2026-03-23', 4, 'PRD-003',  4, 36500.00),
    ('2026-03-23', 4, 'PRD-004',  4, 31000.00),
    ('2026-03-23', 4, 'PRD-010',  2, 19500.00),
    ('2026-03-30', 1, 'PRD-001',  2, 68000.00),
    ('2026-03-30', 1, 'PRD-005', 10, 12000.00),
    ('2026-03-30', 1, 'PRD-002',  5, 17000.00)
) AS v(tanggal, pelanggan_id, kode_produk, qty, harga)
JOIN pesanan p ON p.pelanggan_id = v.pelanggan_id
              AND p.tanggal = v.tanggal::date
JOIN produk pr ON pr.kode = v.kode_produk;

-- kurangi stok seluruh produk yang terjual (pola set-based)
UPDATE produk pr
SET stok = pr.stok - d.qty_terjual
FROM (
    SELECT produk_id, SUM(qty) AS qty_terjual
    FROM pesanan_item
    GROUP BY produk_id
) d
WHERE d.produk_id = pr.id;

COMMIT;

-- kondisi akhir kuartal untuk pemeriksaan
SELECT kode, stok
FROM produk
ORDER BY id;

SELECT count(DISTINCT p.id) AS jumlah_pesanan, sum(i.subtotal) AS total_omzet
FROM pesanan p
JOIN pesanan_item i ON i.pesanan_id = p.id;

Output yang Dihasilkan:

text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
INSERT 0 4
INSERT 0 10
INSERT 0 6
BEGIN
INSERT 0 12
INSERT 0 29
UPDATE 10
COMMIT
  kode   | stok 
---------+------
 PRD-001 |   40
 PRD-002 |   60
 PRD-003 |   35
 PRD-004 |   25
 PRD-005 |   80
 PRD-006 |   45
 PRD-007 |  150
 PRD-008 |  100
 PRD-009 |   30
 PRD-010 |   22
(10 rows)

 jumlah_pesanan | total_omzet 
----------------+-------------
             12 |  2767500.00
(1 row)

Perhatikan satu baris yang berbeda dari lainnya: pesanan 21 Februari memuat Kopi Sachet seharga 11500, bukan 12000 — harga promo yang hanya berlaku saat itu. Nilai itu tersimpan di kolom snapshot harga_saat_transaksi, dan seluruh analisis margin di bawah akan membacanya apa adanya. Dua angka penutup menjadi patokan bagian ini: dua belas pesanan menghasilkan omzet kuartal Rp2.767.500,00.

Omzet Bulanan #

Laporan pertama adalah pertanyaan paling dasar pemilik toko: bagaimana pergerakan penjualan bulan ke bulan? date_trunc('month', ...) memotong tanggal ke awal bulan sehingga GROUP BY mengelompokkan per bulan, lalu window function menjumlahkan berjalan:

sql
penjualan-bulanan.sql
-- penjualan-bulanan.sql — omzet per bulan dengan date_trunc, rata-rata
-- per pesanan, dan omzet kumulatif dengan window function.

WITH bulanan AS (
    SELECT date_trunc('month', p.tanggal) AS bulan,
           count(DISTINCT p.id)            AS jumlah_pesanan,
           sum(i.subtotal)                 AS omzet
    FROM pesanan p
    JOIN pesanan_item i ON i.pesanan_id = p.id
    WHERE p.status = 'selesai'
    GROUP BY 1
)
SELECT to_char(bulan, 'YYYY-MM')      AS bulan,
       jumlah_pesanan,
       omzet,
       round(omzet / jumlah_pesanan, 2) AS rata_per_pesanan,
       sum(omzet) OVER (ORDER BY bulan) AS omzet_kumulatif
FROM bulanan
ORDER BY bulan;

-- Laporan semester: bulan tanpa penjualan (April-Juni) tetap tampil
-- dengan angka nol. generate_series membangkitkan enam baris bulan,
-- lalu LEFT JOIN menjaga baris bulan yang tidak punya pasangan pesanan;
-- COALESCE mengubah SUM yang kosong menjadi nol.
SELECT to_char(b.bulan, 'YYYY-MM')     AS bulan,
       count(DISTINCT p.id)            AS jumlah_pesanan,
       COALESCE(sum(i.subtotal), 0.00) AS omzet
FROM generate_series(DATE '2026-01-01', DATE '2026-06-01', interval '1 month') AS b(bulan)
LEFT JOIN pesanan p ON date_trunc('month', p.tanggal) = b.bulan
                   AND p.status = 'selesai'
LEFT JOIN pesanan_item i ON i.pesanan_id = p.id
GROUP BY b.bulan
ORDER BY b.bulan;

Output yang Dihasilkan:

text
  bulan  | jumlah_pesanan |   omzet    | rata_per_pesanan | omzet_kumulatif 
---------+----------------+------------+------------------+-----------------
 2026-01 |              3 |  616500.00 |        205500.00 |       616500.00
 2026-02 |              4 |  815500.00 |        203875.00 |      1432000.00
 2026-03 |              5 | 1335500.00 |        267100.00 |      2767500.00
(3 rows)

  bulan  | jumlah_pesanan |   omzet    
---------+----------------+------------
 2026-01 |              3 |  616500.00
 2026-02 |              4 |  815500.00
 2026-03 |              5 | 1335500.00
 2026-04 |              0 |       0.00
 2026-05 |              0 |       0.00
 2026-06 |              0 |       0.00
(6 rows)

Tren kuartal terbaca langsung: omzet naik dari 616500 (3 pesanan) ke 815500 (4 pesanan) lalu melompat ke 1335500 (5 pesanan), dengan omzet kumulatif yang menutup di 2767500 — dan rata-rata per pesanan naik tajam di Maret (267100 dari sekitar 205000). sum(omzet) OVER (ORDER BY bulan) adalah running total: setiap baris menjumlahkan dirinya dengan semua bulan sebelumnya.

Query kedua membenahi kelemahan senyap GROUP BY: bulan tanpa penjualan tidak punya baris, jadi ia lenyap dari laporan — padahal “nol” dan “tidak ada baris” adalah dua informasi berbeda bagi pemilik toko. generate_series membangkitkan enam baris bulan dari Januari sampai Juni 2026 sebagai sisi kiri LEFT JOIN, sehingga April sampai Juni tetap tampil dengan nol pesanan dan omzet 0.00 dari COALESCE. Pola tiga alat ini — series + LEFT JOIN + COALESCE — adalah resep baku laporan periodik.

Produk Terlaris dengan Dua Ukuran #

“Produk terlaris” ternyata punya dua jawaban, tergantung ukurannya: jumlah unit atau nilai uang. Query berikut menghitung keduanya sekaligus dan memberi peringkat dengan dua kolom rank():

sql
peringkat-produk.sql
-- peringkat-produk.sql — produk terlaris dengan dua ukuran sekaligus:
-- jumlah unit terjual dan omzet. Dua kolom RANK memperlihatkan bahwa
-- peringkat bisa berbeda tergantung ukuran yang dipakai.

SELECT pr.kode,
       pr.nama,
       k.nama AS kategori,
       sum(i.qty)      AS qty_terjual,
       sum(i.subtotal) AS omzet,
       rank() OVER (ORDER BY sum(i.qty) DESC)      AS peringkat_qty,
       rank() OVER (ORDER BY sum(i.subtotal) DESC) AS peringkat_omzet
FROM pesanan_item i
JOIN produk pr   ON pr.id = i.produk_id
JOIN kategori k  ON k.id = pr.kategori_id
JOIN pesanan p   ON p.id = i.pesanan_id AND p.status = 'selesai'
GROUP BY pr.id, pr.kode, pr.nama, k.nama
ORDER BY peringkat_omzet, pr.kode;

Output yang Dihasilkan:

text
  kode   |          nama           |  kategori  | qty_terjual |   omzet   | peringkat_qty | peringkat_omzet 
---------+-------------------------+------------+-------------+-----------+---------------+-----------------
 PRD-005 | Kopi Sachet Instan      | Minuman    |          46 | 548000.00 |             3 |               1
 PRD-001 | Beras Premium 5kg       | Sembako    |           8 | 544000.00 |             6 |               2
 PRD-007 | Air Mineral 600ml       | Minuman    |          96 | 384000.00 |             1 |               3
 PRD-002 | Gula Pasir 1kg          | Sembako    |          20 | 340000.00 |             4 |               4
 PRD-004 | Telur Ayam 1kg          | Sembako    |           9 | 279000.00 |             5 |               5
 PRD-003 | Minyak Goreng 2L        | Sembako    |           7 | 255500.00 |             7 |               6
 PRD-008 | Mie Instan Goreng       | Makanan    |          70 | 245000.00 |             2 |               7
 PRD-010 | Sabun Cuci Piring 780ml | Kebersihan |           5 |  97500.00 |             8 |               8
 PRD-009 | Biskuit Cokelat         | Makanan    |           5 |  47500.00 |             8 |               9
 PRD-006 | Teh Celup 50 kantong    | Minuman    |           2 |  27000.00 |            10 |              10
(10 rows)

Kontras dua peringkat itu mengajarkan sesuatu yang penting: Air Mineral terlaris secara unit (96 botol, peringkat qty 1) tetapi hanya peringkat 3 secara omzet (384000) karena harganya kecil; sebaliknya Kopi Sachet juara omzet (548000) meski unitnya 46, dan Beras Premium menyusul tipis di peringkat 2 omzet (544000) dengan hanya 8 karung terjual. Kalau manajer membeli stok hanya berdasarkan unit terlaris, rak akan penuh air mineral yang marginnya kecil — keputusan yang lebih baik menunggu hasil analisis margin di akhir bagian. Perhatikan juga rank() yang sama (8) untuk Sabun dan Biskuit pada peringkat omzet: keduanya seri karena memiliki nilai yang sama, dan ORDER BY peringkat_omzet, pr.kode memastikan urutan tampil tetap deterministik.

Pelanggan Terbaik #

Sisi lain laporan adalah pelanggan. Query berikut menggabungkan tiga tabel — pelanggan, pesanan, dan item — untuk menjumlahkan total belanja per pelanggan, lalu memberi peringkat:

sql
pelanggan-terbaik.sql
-- pelanggan-terbaik.sql -- pelanggan dengan total belanja terbesar,
-- lengkap dengan jumlah pesanan dan peringkatnya.

SELECT pl.nama,
       pl.kota,
       count(DISTINCT p.id) AS jumlah_pesanan,
       sum(i.subtotal)      AS total_belanja,
       rank() OVER (ORDER BY sum(i.subtotal) DESC) AS peringkat
FROM pelanggan pl
JOIN pesanan p     ON p.pelanggan_id = pl.id AND p.status = 'selesai'
JOIN pesanan_item i ON i.pesanan_id = p.id
GROUP BY pl.id, pl.nama, pl.kota
ORDER BY peringkat, pl.nama;

Output yang Dihasilkan:

text
     nama     |   kota   | jumlah_pesanan | total_belanja | peringkat 
--------------+----------+----------------+---------------+-----------
 Budi Santoso | Bandung  |              3 |     844500.00 |         1
 Siti Rahayu  | Bandung  |              3 |     651500.00 |         2
 Dewi Lestari | Surabaya |              2 |     415500.00 |         3
 Maya Putri   | Jakarta  |              1 |     378000.00 |         4
 Andi Wijaya  | Jakarta  |              2 |     266000.00 |         5
 Rudi Hartono | Bandung  |              1 |     212000.00 |         6
(6 rows)

Budi Santoso adalah pelanggan terbaik kuartal ini: tiga pesanan senilai 844500, unggul dari Siti Rahayu yang juga tiga pesanan tetapi totalnya 651500 — jumlah pesanan sama, nilai belanja berbeda. Di ujung lain, Rudi Hartono sekali belanja 212000, dan justru pesanan Rudi yang memuat kopi promo. Syarat join p.status = 'selesai' sengaja diletakkan di klausa ON, bukan WHERE: dengan INNER JOIN keduanya setara, tetapi pola menaruh syarat relasi di ON membuat query tinggal diganti JOIN menjadi LEFT JOIN untuk mengikutkan pelanggan yang belum pernah belanja sekalipun — peringkat pelanggan tidak aktif tetap bisa dipantau.

Analisis Keranjang: Produk yang Dibeli Bersama #

Pertanyaan paling menarik bagi tata letak toko: produk apa yang paling sering muncul bersama dalam satu keranjang? Jawabannya menuntut membandingkan tiap baris item dengan baris item lain di pesanan yang sama — pekerjaan self-join:

sql
analisis-keranjang.sql
-- analisis-keranjang.sql — pasangan produk yang paling sering dibeli
-- bersama dalam satu pesanan (market basket analysis).
-- Triknya: SELF JOIN pesanan_item ke dirinya sendiri pada pesanan yang
-- sama, dengan syarat i1.produk_id < i2.produk_id agar tiap pasangan
-- hanya dihitung sekali dan produk tidak dipasangkan dengan dirinya.

SELECT p1.nama AS produk_a,
       p2.nama AS produk_b,
       count(DISTINCT i1.pesanan_id) AS sering_dibeli_bersama
FROM pesanan_item i1
JOIN pesanan_item i2
     ON i1.pesanan_id = i2.pesanan_id
    AND i1.produk_id < i2.produk_id
JOIN produk p1 ON p1.id = i1.produk_id
JOIN produk p2 ON p2.id = i2.produk_id
GROUP BY p1.nama, p2.nama
HAVING count(DISTINCT i1.pesanan_id) >= 2
ORDER BY sering_dibeli_bersama DESC, p1.nama, p2.nama;

Output yang Dihasilkan:

text
     produk_a      |      produk_b      | sering_dibeli_bersama 
-------------------+--------------------+-----------------------
 Gula Pasir 1kg    | Kopi Sachet Instan |                     4
 Air Mineral 600ml | Mie Instan Goreng  |                     2
 Beras Premium 5kg | Gula Pasir 1kg     |                     2
 Beras Premium 5kg | Kopi Sachet Instan |                     2
(4 rows)

Bedah triknya, karena ini query paling padat di bagian ini:

JOIN pesanan_item i2 ON i1.pesanan_id = i2.pesanan_id memasangkan setiap baris item dengan semua baris item lain di pesanan yang sama — inilah self-join yang dikenal dari chapter Self Join , dipakai pada tabel transaksi, bukan tabel referensi.
i1.produk_id < i2.produk_id melakukan dua pekerjaan sekaligus: mencegah produk dipasangkan dengan dirinya sendiri (id tidak bisa lebih kecil dari dirinya), dan memastikan tiap pasangan hanya muncul sekali (pasangan A-B dihitung, B-A tidak) — bandingkan dengan tanda != yang akan menghitung tiap pasangan dua kali.
count(DISTINCT i1.pesanan_id) menghitung jumlah pesanan yang memuat pasangan itu, bukan jumlah kemunculan baris — satu pasangan dalam satu pesanan tetap dihitung sekali.
HAVING >= 2 menyaring pasangan yang baru sekali kebersamaannya — kebetulan belanja sekali tidak layak disebut pola.

Hasilnya: Gula dan Kopi dibeli bersama dalam empat dari dua belas pesanan — pola belanja sembako pagi yang paling kuat, layak diletakkan berdampingan atau dijadikan paket bundling. Tiga pasangan lain (Air Mineral dengan Mie, Beras dengan Gula, Beras dengan Kopi) masing-masing dua kali.

Margin Laba Kotor per Produk #

Laporan penutup menjawab pertanyaan yang sebenarnya: bukan seberapa laris, tetapi berapa untung. Margin dihitung dari selisih harga saat transaksi dengan harga beli — dan di sinilah kolom snapshot dari Bagian 1 menunaikan janjinya:

sql
margin-analisis.sql
-- margin-analisis.sql — laba kotor per produk: selisih harga jual saat
-- transaksi dengan harga beli. Pesanan promo ikut terhitung benar karena
-- margin memakai kolom snapshot harga_saat_transaksi, bukan harga jual
-- terkini di tabel produk.

SELECT pr.kode,
       pr.nama,
       k.nama AS kategori,
       sum(i.qty)      AS qty_terjual,
       sum(i.subtotal) AS omzet,
       sum((i.harga_saat_transaksi - pr.harga_beli) * i.qty) AS margin,
       round(100.0
             * sum((i.harga_saat_transaksi - pr.harga_beli) * i.qty)
             / sum(i.subtotal), 1) AS rasio_margin_pct
FROM pesanan_item i
JOIN produk pr  ON pr.id = i.produk_id
JOIN kategori k ON k.id = pr.kategori_id
JOIN pesanan p  ON p.id = i.pesanan_id AND p.status = 'selesai'
GROUP BY pr.id, pr.kode, pr.nama, k.nama
ORDER BY margin DESC, pr.kode;

Output yang Dihasilkan:

text
  kode   |          nama           |  kategori  | qty_terjual |   omzet   |  margin   | rasio_margin_pct 
---------+-------------------------+------------+-------------+-----------+-----------+------------------
 PRD-007 | Air Mineral 600ml       | Minuman    |          96 | 384000.00 | 144000.00 |             37.5
 PRD-005 | Kopi Sachet Instan      | Minuman    |          46 | 548000.00 | 134000.00 |             24.5
 PRD-001 | Beras Premium 5kg       | Sembako    |           8 | 544000.00 |  56000.00 |             10.3
 PRD-008 | Mie Instan Goreng       | Makanan    |          70 | 245000.00 |  49000.00 |             20.0
 PRD-002 | Gula Pasir 1kg          | Sembako    |          20 | 340000.00 |  40000.00 |             11.8
 PRD-004 | Telur Ayam 1kg          | Sembako    |           9 | 279000.00 |  36000.00 |             12.9
 PRD-003 | Minyak Goreng 2L        | Sembako    |           7 | 255500.00 |  31500.00 |             12.3
 PRD-010 | Sabun Cuci Piring 780ml | Kebersihan |           5 |  97500.00 |  17500.00 |             17.9
 PRD-009 | Biskuit Cokelat         | Makanan    |           5 |  47500.00 |  10000.00 |             21.1
 PRD-006 | Teh Celup 50 kantong    | Minuman    |           2 |  27000.00 |   5000.00 |             18.5
(10 rows)

Kolom margin menjumlahkan (harga_saat_transaksi - harga_beli) * qty per produk. Bila query ini memakai pr.harga_jual alih-alih kolom snapshot, pesanan promo Rudi akan dihitung seolah terjual 12000 padahal kenyataannya 11500 — omzet dan margin kopi menggelembung 4000. Dengan snapshot, margin Kopi tercatat 134000 dengan rasio 24,5 persen yang memuat pesanan promo secara jujur.

Membaca laporan margin bersama peringkat terlaris menghasilkan keputusan belanja yang sehat. Air Mineral menyumbang margin terbesar (144000, rasio 37,5 persen) — produk bervolume besar sekaligus bermargin tebal, layak selalu tersedia. Mie Instan terlaris unit kedua tetapi marginnya hanya 49000 (20 persen); Beras omzet besar namun rasio tertipis (10,3 persen) — penarik pembeli, bukan penambah laba. Menjumlahkan seluruh baris memberi laba kotor kuartal Rp523.000 dari omzet Rp2.767.500, sekitar 18,9 persen — angka yang layak dibandingkan antarkuartal dengan query yang sama.

Database penjualan Toko Maju Jaya kini tuntas dari skema hingga analisis: transaksi atomik yang menjaga uang dan stok, snapshot harga yang menjaga sejarah, dan laporan yang mengubah catatan menjadi keputusan. Proyek berikutnya di section ini berpindah ke ranah yang lebih personal — Proyek: Database Keuangan Pribadi — dengan tantangan tipe data yang lebih sensitif lagi: uang dalam pecahan kecil dan anggaran yang boleh dilanggar sekali sebulan.

Referensi #

Date/Time Functions and Operators — date_trunc untuk agregasi per periode.
Set Returning Functions — generate_series sebagai pengisi baris periode kosong.
Window Functions — rank() dan sum() OVER untuk peringkat dan total berjalan.
Aggregate Functions — count(DISTINCT ...) dan sum() untuk laporan agregasi.

Berkas Sumber

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

sql
-- analisis-keranjang.sql — pasangan produk yang paling sering dibeli
-- bersama dalam satu pesanan (market basket analysis).
-- Triknya: SELF JOIN pesanan_item ke dirinya sendiri pada pesanan yang
-- sama, dengan syarat i1.produk_id < i2.produk_id agar tiap pasangan
-- hanya dihitung sekali dan produk tidak dipasangkan dengan dirinya.

SELECT p1.nama AS produk_a,
       p2.nama AS produk_b,
       count(DISTINCT i1.pesanan_id) AS sering_dibeli_bersama
FROM pesanan_item i1
JOIN pesanan_item i2
     ON i1.pesanan_id = i2.pesanan_id
    AND i1.produk_id < i2.produk_id
JOIN produk p1 ON p1.id = i1.produk_id
JOIN produk p2 ON p2.id = i2.produk_id
GROUP BY p1.nama, p2.nama
HAVING count(DISTINCT i1.pesanan_id) >= 2
ORDER BY sering_dibeli_bersama DESC, p1.nama, p2.nama;

sql
-- margin-analisis.sql — laba kotor per produk: selisih harga jual saat
-- transaksi dengan harga beli. Pesanan promo ikut terhitung benar karena
-- margin memakai kolom snapshot harga_saat_transaksi, bukan harga jual
-- terkini di tabel produk.

SELECT pr.kode,
       pr.nama,
       k.nama AS kategori,
       sum(i.qty)      AS qty_terjual,
       sum(i.subtotal) AS omzet,
       sum((i.harga_saat_transaksi - pr.harga_beli) * i.qty) AS margin,
       round(100.0
             * sum((i.harga_saat_transaksi - pr.harga_beli) * i.qty)
             / sum(i.subtotal), 1) AS rasio_margin_pct
FROM pesanan_item i
JOIN produk pr  ON pr.id = i.produk_id
JOIN kategori k ON k.id = pr.kategori_id
JOIN pesanan p  ON p.id = i.pesanan_id AND p.status = 'selesai'
GROUP BY pr.id, pr.kode, pr.nama, k.nama
ORDER BY margin DESC, pr.kode;

sql
-- pelanggan-terbaik.sql -- pelanggan dengan total belanja terbesar,
-- lengkap dengan jumlah pesanan dan peringkatnya.

SELECT pl.nama,
       pl.kota,
       count(DISTINCT p.id) AS jumlah_pesanan,
       sum(i.subtotal)      AS total_belanja,
       rank() OVER (ORDER BY sum(i.subtotal) DESC) AS peringkat
FROM pelanggan pl
JOIN pesanan p     ON p.pelanggan_id = pl.id AND p.status = 'selesai'
JOIN pesanan_item i ON i.pesanan_id = p.id
GROUP BY pl.id, pl.nama, pl.kota
ORDER BY peringkat, pl.nama;

sql
-- penjualan-bulanan.sql — omzet per bulan dengan date_trunc, rata-rata
-- per pesanan, dan omzet kumulatif dengan window function.

WITH bulanan AS (
    SELECT date_trunc('month', p.tanggal) AS bulan,
           count(DISTINCT p.id)            AS jumlah_pesanan,
           sum(i.subtotal)                 AS omzet
    FROM pesanan p
    JOIN pesanan_item i ON i.pesanan_id = p.id
    WHERE p.status = 'selesai'
    GROUP BY 1
)
SELECT to_char(bulan, 'YYYY-MM')      AS bulan,
       jumlah_pesanan,
       omzet,
       round(omzet / jumlah_pesanan, 2) AS rata_per_pesanan,
       sum(omzet) OVER (ORDER BY bulan) AS omzet_kumulatif
FROM bulanan
ORDER BY bulan;

-- Laporan semester: bulan tanpa penjualan (April-Juni) tetap tampil
-- dengan angka nol. generate_series membangkitkan enam baris bulan,
-- lalu LEFT JOIN menjaga baris bulan yang tidak punya pasangan pesanan;
-- COALESCE mengubah SUM yang kosong menjadi nol.
SELECT to_char(b.bulan, 'YYYY-MM')     AS bulan,
       count(DISTINCT p.id)            AS jumlah_pesanan,
       COALESCE(sum(i.subtotal), 0.00) AS omzet
FROM generate_series(DATE '2026-01-01', DATE '2026-06-01', interval '1 month') AS b(bulan)
LEFT JOIN pesanan p ON date_trunc('month', p.tanggal) = b.bulan
                   AND p.status = 'selesai'
LEFT JOIN pesanan_item i ON i.pesanan_id = p.id
GROUP BY b.bulan
ORDER BY b.bulan;

sql
-- peringkat-produk.sql — produk terlaris dengan dua ukuran sekaligus:
-- jumlah unit terjual dan omzet. Dua kolom RANK memperlihatkan bahwa
-- peringkat bisa berbeda tergantung ukuran yang dipakai.

SELECT pr.kode,
       pr.nama,
       k.nama AS kategori,
       sum(i.qty)      AS qty_terjual,
       sum(i.subtotal) AS omzet,
       rank() OVER (ORDER BY sum(i.qty) DESC)      AS peringkat_qty,
       rank() OVER (ORDER BY sum(i.subtotal) DESC) AS peringkat_omzet
FROM pesanan_item i
JOIN produk pr   ON pr.id = i.produk_id
JOIN kategori k  ON k.id = pr.kategori_id
JOIN pesanan p   ON p.id = i.pesanan_id AND p.status = 'selesai'
GROUP BY pr.id, pr.kode, pr.nama, k.nama
ORDER BY peringkat_omzet, pr.kode;

sql
-- siapkan.sql — fondasi Bagian 4: skema dari Bagian 1 + data master
-- + dua belas pesanan lengkap sepanjang kuartal I 2026 (29 item).
-- Bagian ini dimaksudkan dapat dijalankan sendiri dari database kosong.

CREATE TABLE kategori (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text   NOT NULL UNIQUE
);

CREATE TABLE produk (
    id          integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kode        text           NOT NULL UNIQUE,
    nama        text           NOT NULL,
    kategori_id integer        NOT NULL REFERENCES kategori (id),
    harga_beli  numeric(12, 2) NOT NULL CHECK (harga_beli > 0),
    harga_jual  numeric(12, 2) NOT NULL CHECK (harga_jual > 0),
    stok        integer        NOT NULL CHECK (stok >= 0)
);

CREATE TABLE pelanggan (
    id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama  text   NOT NULL,
    email text   NOT NULL UNIQUE,
    kota  text   NOT NULL
);

CREATE TABLE pesanan (
    id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pelanggan_id integer NOT NULL REFERENCES pelanggan (id),
    tanggal      date    NOT NULL,
    status       text    NOT NULL DEFAULT 'baru'
                 CHECK (status IN ('baru', 'diproses', 'selesai', 'batal'))
);

CREATE TABLE pesanan_item (
    id                   integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pesanan_id           integer        NOT NULL REFERENCES pesanan (id) ON DELETE CASCADE,
    produk_id            integer        NOT NULL REFERENCES produk (id),
    qty                  integer        NOT NULL CHECK (qty > 0),
    harga_saat_transaksi numeric(12, 2) NOT NULL,
    subtotal             numeric(12, 2) GENERATED ALWAYS AS (qty * harga_saat_transaksi) STORED
);

CREATE INDEX idx_pesanan_tanggal       ON pesanan (tanggal);
CREATE INDEX idx_pesanan_pelanggan     ON pesanan (pelanggan_id);
CREATE INDEX idx_pesanan_item_pesanan  ON pesanan_item (pesanan_id);
CREATE INDEX idx_pesanan_item_produk   ON pesanan_item (produk_id);


INSERT INTO kategori (nama) VALUES
    ('Sembako'),
    ('Minuman'),
    ('Makanan'),
    ('Kebersihan');

-- stok awal kuartal: jumlah akhir + total terjual sepanjang kuartal
INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok) VALUES
    ('PRD-001', 'Beras Premium 5kg',        1, 61000.00, 68000.00,  48),
    ('PRD-002', 'Gula Pasir 1kg',           1, 15000.00, 17000.00,  80),
    ('PRD-003', 'Minyak Goreng 2L',         1, 32000.00, 36500.00,  42),
    ('PRD-004', 'Telur Ayam 1kg',           1, 27000.00, 31000.00,  34),
    ('PRD-005', 'Kopi Sachet Instan',       2,  9000.00, 12000.00, 126),
    ('PRD-006', 'Teh Celup 50 kantong',     2, 11000.00, 13500.00,  47),
    ('PRD-007', 'Air Mineral 600ml',        2,  2500.00,  4000.00, 246),
    ('PRD-008', 'Mie Instan Goreng',        3,  2800.00,  3500.00, 170),
    ('PRD-009', 'Biskuit Cokelat',          3,  7500.00,  9500.00,  35),
    ('PRD-010', 'Sabun Cuci Piring 780ml',  4, 16000.00, 19500.00,  27);

INSERT INTO pelanggan (nama, email, kota) VALUES
    ('Budi Santoso', '[email protected]',  'Bandung'),
    ('Siti Rahayu',  '[email protected]',  'Bandung'),
    ('Andi Wijaya',  '[email protected]',  'Jakarta'),
    ('Dewi Lestari', '[email protected]',  'Surabaya'),
    ('Rudi Hartono', '[email protected]',  'Bandung'),
    ('Maya Putri',   '[email protected]',  'Jakarta');

-- dua belas pesanan sepanjang Januari-Maret 2026, semuanya selesai.
-- Pesanan 7 memuat harga promo Kopi 11500 (snapshot harga saat transaksi).
BEGIN;

INSERT INTO pesanan (pelanggan_id, tanggal, status) VALUES
    (2, DATE '2026-01-05', 'selesai'),
    (1, DATE '2026-01-12', 'selesai'),
    (3, DATE '2026-01-25', 'selesai'),
    (2, DATE '2026-02-03', 'selesai'),
    (4, DATE '2026-02-08', 'selesai'),
    (1, DATE '2026-02-15', 'selesai'),
    (5, DATE '2026-02-21', 'selesai'),
    (2, DATE '2026-03-02', 'selesai'),
    (6, DATE '2026-03-09', 'selesai'),
    (3, DATE '2026-03-16', 'selesai'),
    (4, DATE '2026-03-23', 'selesai'),
    (1, DATE '2026-03-30', 'selesai');

INSERT INTO pesanan_item (pesanan_id, produk_id, qty, harga_saat_transaksi)
SELECT p.id, pr.id, v.qty, v.harga
FROM (VALUES
    ('2026-01-05', 2, 'PRD-005', 10, 12000.00),
    ('2026-01-05', 2, 'PRD-002',  5, 17000.00),
    ('2026-01-12', 1, 'PRD-001',  2, 68000.00),
    ('2026-01-12', 1, 'PRD-003',  3, 36500.00),
    ('2026-01-25', 3, 'PRD-008', 20,  3500.00),
    ('2026-01-25', 3, 'PRD-007', 24,  4000.00),
    ('2026-02-03', 2, 'PRD-005', 12, 12000.00),
    ('2026-02-03', 2, 'PRD-002',  4, 17000.00),
    ('2026-02-03', 2, 'PRD-006',  2, 13500.00),
    ('2026-02-08', 4, 'PRD-010',  3, 19500.00),
    ('2026-02-08', 4, 'PRD-007', 12,  4000.00),
    ('2026-02-15', 1, 'PRD-001',  1, 68000.00),
    ('2026-02-15', 1, 'PRD-004',  5, 31000.00),
    ('2026-02-15', 1, 'PRD-008', 10,  3500.00),
    ('2026-02-21', 5, 'PRD-005',  8, 11500.00),
    ('2026-02-21', 5, 'PRD-007', 30,  4000.00),
    ('2026-03-02', 2, 'PRD-008', 25,  3500.00),
    ('2026-03-02', 2, 'PRD-007', 30,  4000.00),
    ('2026-03-09', 6, 'PRD-001',  3, 68000.00),
    ('2026-03-09', 6, 'PRD-002',  6, 17000.00),
    ('2026-03-09', 6, 'PRD-005',  6, 12000.00),
    ('2026-03-16', 3, 'PRD-008', 15,  3500.00),
    ('2026-03-16', 3, 'PRD-009',  5,  9500.00),
    ('2026-03-23', 4, 'PRD-003',  4, 36500.00),
    ('2026-03-23', 4, 'PRD-004',  4, 31000.00),
    ('2026-03-23', 4, 'PRD-010',  2, 19500.00),
    ('2026-03-30', 1, 'PRD-001',  2, 68000.00),
    ('2026-03-30', 1, 'PRD-005', 10, 12000.00),
    ('2026-03-30', 1, 'PRD-002',  5, 17000.00)
) AS v(tanggal, pelanggan_id, kode_produk, qty, harga)
JOIN pesanan p ON p.pelanggan_id = v.pelanggan_id
              AND p.tanggal = v.tanggal::date
JOIN produk pr ON pr.kode = v.kode_produk;

-- kurangi stok seluruh produk yang terjual (pola set-based)
UPDATE produk pr
SET stok = pr.stok - d.qty_terjual
FROM (
    SELECT produk_id, SUM(qty) AS qty_terjual
    FROM pesanan_item
    GROUP BY produk_id
) d
WHERE d.produk_id = pr.id;

COMMIT;

-- kondisi akhir kuartal untuk pemeriksaan
SELECT kode, stok
FROM produk
ORDER BY id;

SELECT count(DISTINCT p.id) AS jumlah_pesanan, sum(i.subtotal) AS total_omzet
FROM pesanan p
JOIN pesanan_item i ON i.pesanan_id = p.id;