Bagian 4: Laporan dan Eksport

-- analisis.sql — Analisis Penjualan
-- Bagian 3: query analisis penjualan semester I 2025.
-- Jalankan: sqlite3 penjualan.db < analisis.sql
-- Prasyarat: schema.sql dan import.sql sudah dijalankan.

.headers on
.mode column

-- 1. Omzet per bulan: berapa pendapatan tiap bulan?
.print === 1. Omzet per Bulan ===
SELECT strftime('%Y-%m', tanggal) AS bulan,
       COUNT(*)          AS transaksi,
       SUM(jumlah)       AS item_terjual,
       SUM(total_harga)  AS omzet
FROM penjualan
GROUP BY bulan
ORDER BY bulan;

.print
-- 2. Lima produk dengan omzet terbesar.
.print === 2. Lima Produk Teratas ===
SELECT pr.nama,
       pr.kategori,
       SUM(pe.jumlah)      AS item_terjual,
       SUM(pe.total_harga) AS omzet
FROM penjualan pe
JOIN produk pr ON pr.id = pe.produk_id
GROUP BY pr.id
ORDER BY omzet DESC
LIMIT 5;

.print
-- 3. Pangsa omzet tiap kategori dalam persen.
.print === 3. Pangsa Kategori ===
WITH omzet_kategori AS (
    SELECT pr.kategori, SUM(pe.total_harga) AS omzet
    FROM penjualan pe
    JOIN produk pr ON pr.id = pe.produk_id
    GROUP BY pr.kategori
)
SELECT kategori,
       omzet,
       ROUND(omzet * 100.0 / (SELECT SUM(omzet) FROM omzet_kategori), 1) AS persen
FROM omzet_kategori
ORDER BY omzet DESC;

.print
-- 4. Omzet kumulatif: total berjalan dari Januari ke bulan berjalan.
.print === 4. Omzet Kumulatif Bulanan ===
WITH omzet_bulanan AS (
    SELECT strftime('%Y-%m', tanggal) AS bulan,
           SUM(total_harga)           AS omzet
    FROM penjualan
    GROUP BY bulan
)
SELECT bulan,
       omzet,
       SUM(omzet) OVER (ORDER BY bulan) AS omzet_kumulatif
FROM omzet_bulanan
ORDER BY bulan;

.print
-- 5. Peringkat produk di dalam kategorinya masing-masing.
.print === 5. Peringkat Produk per Kategori ===
WITH omzet_produk AS (
    SELECT pr.kategori, pr.nama, SUM(pe.total_harga) AS omzet
    FROM penjualan pe
    JOIN produk pr ON pr.id = pe.produk_id
    GROUP BY pr.id
)
SELECT kategori,
       nama,
       omzet,
       RANK() OVER (PARTITION BY kategori ORDER BY omzet DESC) AS peringkat
FROM omzet_produk
ORDER BY kategori, peringkat;

.print
-- 6. Pertumbuhan omzet bulan-ke-bulan dalam persen.
.print === 6. Pertumbuhan Bulanan ===
WITH omzet_bulanan AS (
    SELECT strftime('%Y-%m', tanggal) AS bulan,
           SUM(total_harga)           AS omzet
    FROM penjualan
    GROUP BY bulan
)
SELECT bulan,
       omzet,
       LAG(omzet) OVER (ORDER BY bulan) AS omzet_bulan_lalu,
       ROUND((omzet - LAG(omzet) OVER (ORDER BY bulan)) * 100.0
             / LAG(omzet) OVER (ORDER BY bulan), 1) AS pertumbuhan_persen
FROM omzet_bulanan
ORDER BY bulan;

.print
-- 7. Statistik harian: hari aktif dan nilai rata-rata transaksi.
.print === 7. Statistik Harian ===
SELECT COUNT(*)                                  AS transaksi,
       COUNT(DISTINCT tanggal)                   AS hari_aktif,
       ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT tanggal), 2) AS transaksi_per_hari,
       ROUND(AVG(total_harga))                   AS rata_rata_per_transaksi
FROM penjualan;

-- import.sql — Analisis Penjualan
-- Bagian 2: import data_penjualan.csv ke tabel penjualan + verifikasi.
-- Jalankan: sqlite3 penjualan.db < import.sql
-- Prasyarat: schema.sql sudah dijalankan pada database yang sama.

PRAGMA foreign_keys = ON;

.headers on
.mode column

-- 1. Kosongkan tabel supaya skrip aman diulang kapan pun.
DELETE FROM penjualan;

-- 2. Import 60 baris data: format CSV, baris judul dilewati.
.import --csv --skip 1 data_penjualan.csv penjualan

-- 3. Verifikasi jumlah baris.
.print Jumlah baris:
SELECT COUNT(*) AS baris_penjualan FROM penjualan;

-- 4. Verifikasi tipe: .import menghasilkan teks, type affinity
--    kolom INTEGER harus mengonversinya menjadi integer.
.print Tipe data kolom (harus integer):
SELECT DISTINCT typeof(produk_id) AS produk_id,
                typeof(jumlah)    AS jumlah,
                typeof(total_harga) AS total_harga
FROM penjualan;

-- 5. Verifikasi konsistensi: total_harga = jumlah x harga_satuan.
.print Baris dengan total janggal (harus 0):
SELECT COUNT(*) AS baris_janggal
FROM penjualan pe
JOIN produk pr ON pr.id = pe.produk_id
WHERE pe.total_harga <> pe.jumlah * pr.harga_satuan;

-- 6. Verifikasi rentang tanggal dan hari aktif.
.print Rentang tanggal:
SELECT MIN(tanggal) AS tanggal_awal,
       MAX(tanggal) AS tanggal_akhir,
       COUNT(DISTINCT tanggal) AS hari_aktif
FROM penjualan;

-- laporan.sql — Analisis Penjualan
-- Bagian 4: laporan akhir siap baca + eksport ke CSV dan JSON.
-- Jalankan: sqlite3 penjualan.db < laporan.sql
-- Prasyarat: schema.sql dan import.sql sudah dijalankan.

PRAGMA foreign_keys = ON;

-- Dua tampilan sementara (TEMP VIEW): hidup hanya selama sesi
-- CLI ini, jadi isi database tidak berubah sama sekali.
CREATE TEMP VIEW v_omzet_bulanan AS
SELECT strftime('%Y-%m', tanggal) AS bulan,
       COUNT(*)          AS transaksi,
       SUM(total_harga)  AS omzet
FROM penjualan
GROUP BY bulan;

CREATE TEMP VIEW v_omzet_kategori AS
SELECT pr.kategori, SUM(pe.total_harga) AS omzet
FROM penjualan pe
JOIN produk pr ON pr.id = pe.produk_id
GROUP BY pr.kategori;

.headers on
.mode column

.print ===== LAPORAN PENJUALAN - SEMESTER I 2025 =====
.print
.print --- Ringkasan ---
SELECT COUNT(*)                AS transaksi,
       COUNT(DISTINCT tanggal) AS hari_aktif,
       SUM(jumlah)             AS item_terjual,
       SUM(total_harga)        AS total_omzet
FROM penjualan;

.print
.print --- Omzet Bulanan dan Pertumbuhan ---
SELECT bulan,
       transaksi,
       omzet,
       ROUND((omzet - LAG(omzet) OVER (ORDER BY bulan)) * 100.0
             / LAG(omzet) OVER (ORDER BY bulan), 1) AS pertumbuhan_persen
FROM v_omzet_bulanan
ORDER BY bulan;

.print
.print --- Lima Produk Teratas ---
SELECT pr.nama AS produk,
       pr.kategori,
       SUM(pe.jumlah)      AS item_terjual,
       SUM(pe.total_harga) AS omzet
FROM penjualan pe
JOIN produk pr ON pr.id = pe.produk_id
GROUP BY pr.id
ORDER BY omzet DESC
LIMIT 5;

.print
.print --- Pangsa Kategori ---
SELECT kategori,
       omzet,
       ROUND(omzet * 100.0 / (SELECT SUM(omzet) FROM v_omzet_kategori), 1) AS persen
FROM v_omzet_kategori
ORDER BY omzet DESC;

-- Eksport CSV: tabel omzet bulanan untuk dibuka di spreadsheet.
.mode csv
.once laporan-omzet.csv
SELECT bulan,
       transaksi,
       omzet,
       ROUND((omzet - LAG(omzet) OVER (ORDER BY bulan)) * 100.0
             / LAG(omzet) OVER (ORDER BY bulan), 1) AS pertumbuhan_persen
FROM v_omzet_bulanan
ORDER BY bulan;

-- Eksport JSON: omzet per produk untuk konsumen program lain.
-- Semua kolom sengaja integer: nilai REAL akan tercetak dengan
-- representasi lengkapnya di JSON (lihat peringatan pada halaman ini).
.mode json
.once laporan-produk.json
SELECT pr.nama  AS produk,
       pr.kategori,
       SUM(pe.jumlah)      AS item_terjual,
       SUM(pe.total_harga) AS omzet
FROM penjualan pe
JOIN produk pr ON pr.id = pe.produk_id
GROUP BY pr.id
ORDER BY omzet DESC;

-- schema.sql — Analisis Penjualan
-- Bagian 1: skema database analisis penjualan + data master produk.
-- Jalankan: sqlite3 penjualan.db < schema.sql

PRAGMA foreign_keys = ON;

-- Data master: delapan produk tetap yang dijual toko.
-- Kolom kategori memakai CHECK IN agar nilai liar tertolak sejak awal.
CREATE TABLE produk (
    id           INTEGER PRIMARY KEY,
    nama         TEXT    NOT NULL,
    kategori     TEXT    NOT NULL CHECK (kategori IN ('Makanan', 'Minuman', 'Rumah Tangga')),
    harga_satuan INTEGER NOT NULL CHECK (harga_satuan > 0)
);

-- Data transaksi: satu baris = satu transaksi penjualan.
-- total_harga disimpan langsung (jumlah x harga saat transaksi),
-- sehingga riwayat tetap benar meski harga_satuan produk berubah.
CREATE TABLE penjualan (
    id          INTEGER PRIMARY KEY,
    tanggal     TEXT    NOT NULL CHECK (tanggal IS date(tanggal)),
    produk_id   INTEGER NOT NULL REFERENCES produk(id) ON DELETE RESTRICT,
    jumlah      INTEGER NOT NULL CHECK (jumlah > 0),
    total_harga INTEGER NOT NULL CHECK (total_harga >= 0)
);

-- Index untuk dua pola akses utama analisis:
-- rentang/bulan tanggal dan penggabungan dari produk ke penjualan.
CREATE INDEX idx_penjualan_tanggal   ON penjualan(tanggal);
CREATE INDEX idx_penjualan_produk_id ON penjualan(produk_id);

INSERT INTO produk (id, nama, kategori, harga_satuan) VALUES
    (1, 'Kopi Gayo 250 g',           'Minuman',     65000),
    (2, 'Teh Melati 100 g',           'Minuman',     18000),
    (3, 'Gula Aren 500 g',            'Makanan',     25000),
    (4, 'Beras Pandan Wangi 5 kg',   'Makanan',     72000),
    (5, 'Minyak Goreng 2 L',          'Rumah Tangga', 38000),
    (6, 'Sabun Mandi Cair 450 ml',    'Rumah Tangga', 22000),
    (7, 'Pasta Gigi 190 g',           'Rumah Tangga', 16000),
    (8, 'Sarden Kaleng 155 g',        'Makanan',     12000);