Query Pattern untuk Data Analisis

-- Tabel latihan chapter Query Pattern: penjualan_bulanan (24 bulan x 3
-- kanal, 2024-2025, dibangkitkan rumus tetap agar reproducible) dan
-- transaksi (200 pembelian tahun 2025, kategori 4 macam).
-- Rumus musiman: puncak November-Desember meniru lonjakan akhir tahun.
CREATE TABLE penjualan_bulanan (
    bulan      date NOT NULL,
    kanal      text NOT NULL,
    pendapatan numeric(14,2) NOT NULL,
    PRIMARY KEY (bulan, kanal)
);

INSERT INTO penjualan_bulanan (bulan, kanal, pendapatan)
SELECT (DATE '2024-01-01' + (g.i - 1) * INTERVAL '1 month')::date AS bulan,
       k.kanal,
       round((k.basis + g.i * k.tren
              + CASE WHEN g.i % 12 IN (0, 11) THEN k.puncak ELSE 0 END)::numeric, 2)
FROM generate_series(1, 24) AS g(i)
CROSS JOIN (VALUES
    ('web',      12000000, 260000, 4500000),
    ('aplikasi',  8000000, 420000, 3200000),
    ('offline',   6000000, -40000, 1800000)
) AS k(kanal, basis, tren, puncak);

CREATE TABLE transaksi (
    id        integer PRIMARY KEY,
    pelanggan text NOT NULL,
    kategori  text NOT NULL,
    tanggal   date NOT NULL,
    jumlah    numeric(12,2) NOT NULL
);

INSERT INTO transaksi (id, pelanggan, kategori, tanggal, jumlah)
SELECT i,
       (ARRAY['Budi','Siti','Andi','Rina','Tono','Wati','Joko','Mira'])[1 + (i % 8)],
       (ARRAY['Elektronik','Fashion','Buku','Peralatan Rumah'])[1 + (i % 4)],
       (DATE '2025-01-15' + (i % 12) * INTERVAL '1 month')::date,
       (60000 + ((i * 19) % 50) * ((i * 19) % 50) * 350 + (i % 13) * 6000)::numeric(12,2)
FROM generate_series(1, 200) AS i;

-- Ringkasan dataset: dua tahun penjualan dan sebaran transaksi.
SELECT extract(year FROM bulan)::integer AS tahun,
       count(*) AS baris,
       round(sum(pendapatan)) AS total_pendapatan
FROM penjualan_bulanan
GROUP BY 1
ORDER BY 1;

SELECT kategori,
       count(*) AS transaksi,
       min(jumlah) AS terkecil,
       max(jumlah) AS terbesar
FROM transaksi
GROUP BY 1
ORDER BY 1;

CREATE TABLE
INSERT 0 72
CREATE TABLE
INSERT 0 200
 tahun | baris | total_pendapatan 
-------+-------+------------------
  2024 |    36 |        380920000
  2025 |    36 |        473080000
(2 rows)

    kategori     | transaksi | terkecil | terbesar  
-----------------+-----------+----------+-----------
 Buku            |        50 | 71600.00 | 938400.00
 Elektronik      |        50 | 83600.00 | 926400.00
 Fashion         |        50 | 78350.00 | 948350.00
 Peralatan Rumah |        50 | 66350.00 | 936350.00
(4 rows)

-- Pola 1: pertumbuhan bulan-ke-bulan (MoM) dan rata-rata bergerak 3 bulan.
-- Kapan dipakai: laporan tren bulanan -- angka mentah MoM berisik karena
-- musiman, rata-rata bergerak menghaluskannya.
-- Perhatikan urutan: LAG dan AVG dihitung di CTE atas seluruh 24 bulan,
-- filter 2025 diterapkan SETELAHNYA -- memfilter lebih awal memotong
-- bulan Desember 2024 dan merusak MoM Januari.
WITH bulanan AS (
    SELECT bulan, sum(pendapatan) AS pendapatan
    FROM penjualan_bulanan
    GROUP BY bulan
),
ber_laju AS (
    SELECT bulan,
           pendapatan,
           lag(pendapatan) OVER (ORDER BY bulan) AS bulan_lalu,
           round(avg(pendapatan) OVER (
               ORDER BY bulan ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS rata3
    FROM bulanan
)
SELECT to_char(bulan, 'YYYY-MM') AS bulan,
       pendapatan,
       round(100.0 * (pendapatan - bulan_lalu) / bulan_lalu, 1) AS mom_pct,
       rata3 AS rata_gerak_3bulan
FROM ber_laju
WHERE bulan >= DATE '2025-01-01'
ORDER BY bulan;

  bulan  | pendapatan  | mom_pct | rata_gerak_3bulan 
---------+-------------+---------+-------------------
 2025-01 | 34320000.00 |   -20.5 |       40013333.33
 2025-02 | 34960000.00 |     1.9 |       37486666.67
 2025-03 | 35600000.00 |     1.8 |       34960000.00
 2025-04 | 36240000.00 |     1.8 |       35600000.00
 2025-05 | 36880000.00 |     1.8 |       36240000.00
 2025-06 | 37520000.00 |     1.7 |       36880000.00
 2025-07 | 38160000.00 |     1.7 |       37520000.00
 2025-08 | 38800000.00 |     1.7 |       38160000.00
 2025-09 | 39440000.00 |     1.6 |       38800000.00
 2025-10 | 40080000.00 |     1.6 |       39440000.00
 2025-11 | 50220000.00 |    25.3 |       43246666.67
 2025-12 | 50860000.00 |     1.3 |       47053333.33
(12 rows)

-- Pola 2: year-over-year (YoY) -- bulan ini vs bulan yang sama tahun lalu.
-- Kapan dipakai: menilai pertumbuhan tanpa terjebak musiman -- November
-- selalu besar, jadi November hanya adil dibanding November.
--
-- Varian 1: lag(pendapatan, 12) menggeser deret bulanan 12 baris --
-- bulan yang sama setahun sebelumnya jatuh tepat di sebelahnya.
WITH bulanan AS (
    SELECT bulan, sum(pendapatan) AS pendapatan
    FROM penjualan_bulanan
    GROUP BY bulan
),
yoy AS (
    SELECT bulan,
           pendapatan,
           lag(pendapatan, 12) OVER (ORDER BY bulan) AS tahun_lalu
    FROM bulanan
)
SELECT to_char(bulan, 'YYYY-MM') AS bulan,
       pendapatan,
       tahun_lalu,
       round(100.0 * (pendapatan - tahun_lalu) / tahun_lalu, 1) AS yoy_pct
FROM yoy
WHERE bulan >= DATE '2025-01-01'
ORDER BY bulan;

-- Varian 2: self-join pada bulan - 1 tahun. Lebih cocok bila perbandingan
-- per kanal/per kelompok -- join mendobrak langsung ke baris pasangannya
-- tanpa melewati deret lain.
SELECT to_char(p.bulan, 'YYYY-MM') AS bulan,
       p.kanal,
       p.pendapatan,
       s.pendapatan AS tahun_lalu,
       round(100.0 * (p.pendapatan - s.pendapatan) / s.pendapatan, 1) AS yoy_pct
FROM penjualan_bulanan p
INNER JOIN penjualan_bulanan s
        ON s.kanal = p.kanal
       AND s.bulan = (p.bulan - INTERVAL '1 year')::date
WHERE p.kanal = 'aplikasi'
  AND p.bulan >= DATE '2025-01-01'
ORDER BY p.bulan;

  bulan  | pendapatan  | tahun_lalu  | yoy_pct 
---------+-------------+-------------+---------
 2025-01 | 34320000.00 | 26640000.00 |    28.8
 2025-02 | 34960000.00 | 27280000.00 |    28.2
 2025-03 | 35600000.00 | 27920000.00 |    27.5
 2025-04 | 36240000.00 | 28560000.00 |    26.9
 2025-05 | 36880000.00 | 29200000.00 |    26.3
 2025-06 | 37520000.00 | 29840000.00 |    25.7
 2025-07 | 38160000.00 | 30480000.00 |    25.2
 2025-08 | 38800000.00 | 31120000.00 |    24.7
 2025-09 | 39440000.00 | 31760000.00 |    24.2
 2025-10 | 40080000.00 | 32400000.00 |    23.7
 2025-11 | 50220000.00 | 42540000.00 |    18.1
 2025-12 | 50860000.00 | 43180000.00 |    17.8
(12 rows)

  bulan  |  kanal   | pendapatan  | tahun_lalu  | yoy_pct 
---------+----------+-------------+-------------+---------
 2025-01 | aplikasi | 13460000.00 |  8420000.00 |    59.9
 2025-02 | aplikasi | 13880000.00 |  8840000.00 |    57.0
 2025-03 | aplikasi | 14300000.00 |  9260000.00 |    54.4
 2025-04 | aplikasi | 14720000.00 |  9680000.00 |    52.1
 2025-05 | aplikasi | 15140000.00 | 10100000.00 |    49.9
 2025-06 | aplikasi | 15560000.00 | 10520000.00 |    47.9
 2025-07 | aplikasi | 15980000.00 | 10940000.00 |    46.1
 2025-08 | aplikasi | 16400000.00 | 11360000.00 |    44.4
 2025-09 | aplikasi | 16820000.00 | 11780000.00 |    42.8
 2025-10 | aplikasi | 17240000.00 | 12200000.00 |    41.3
 2025-11 | aplikasi | 20860000.00 | 15820000.00 |    31.9
 2025-12 | aplikasi | 21280000.00 | 16240000.00 |    31.0
(12 rows)

-- Pola 3: kumulatif (running total) dan pangsa terhadap total
-- (share of total). Kapan dipakai: target capaian tahun berjalan dan
-- kontribusi tiap kanal terhadap keseluruhan.
--
-- Query 1: pendapatan kumulatif 2025 + persen dari total tahun.
-- sum(...) OVER () tanpa ORDER BY = total seluruh baris (bukan berjalan).
WITH bulanan AS (
    SELECT bulan, sum(pendapatan) AS pendapatan
    FROM penjualan_bulanan
    GROUP BY bulan
),
setahun AS (
    SELECT bulan, pendapatan
    FROM bulanan
    WHERE bulan >= DATE '2025-01-01'
)
SELECT to_char(bulan, 'YYYY-MM') AS bulan,
       pendapatan,
       round(sum(pendapatan) OVER (ORDER BY bulan), 2) AS kumulatif,
       round(100.0 * sum(pendapatan) OVER (ORDER BY bulan)
                   / sum(pendapatan) OVER (), 1) AS persen_tahun
FROM setahun
ORDER BY bulan;

-- Query 2: pangsa kanal atas pendapatan 2025 -- agregat di atas agregat:
-- sum(sum(pendapatan)) OVER () menjumlahkan hasil GROUP BY tiap kanal.
SELECT kanal,
       round(sum(pendapatan), 2) AS total_2025,
       round(100.0 * sum(pendapatan) / sum(sum(pendapatan)) OVER (), 1) AS pangsa_pct
FROM penjualan_bulanan
WHERE bulan >= DATE '2025-01-01'
GROUP BY kanal
ORDER BY total_2025 DESC;

  bulan  | pendapatan  |  kumulatif   | persen_tahun 
---------+-------------+--------------+--------------
 2025-01 | 34320000.00 |  34320000.00 |          7.3
 2025-02 | 34960000.00 |  69280000.00 |         14.6
 2025-03 | 35600000.00 | 104880000.00 |         22.2
 2025-04 | 36240000.00 | 141120000.00 |         29.8
 2025-05 | 36880000.00 | 178000000.00 |         37.6
 2025-06 | 37520000.00 | 215520000.00 |         45.6
 2025-07 | 38160000.00 | 253680000.00 |         53.6
 2025-08 | 38800000.00 | 292480000.00 |         61.8
 2025-09 | 39440000.00 | 331920000.00 |         70.2
 2025-10 | 40080000.00 | 372000000.00 |         78.6
 2025-11 | 50220000.00 | 422220000.00 |         89.2
 2025-12 | 50860000.00 | 473080000.00 |        100.0
(12 rows)

  kanal   |  total_2025  | pangsa_pct 
----------+--------------+------------
 web      | 210720000.00 |       44.5
 aplikasi | 195640000.00 |       41.4
 offline  |  66720000.00 |       14.1
(3 rows)

-- Pola 4: top-k per kategori. Kapan dipakai: "3 transaksi terbesar tiap
-- kategori", "5 produk terlaris tiap gudang" -- LIMIT global hanya
-- mengambil k baris dari seluruh tabel, bukan k per kelompok.
-- row_number di dalam PARTITION kategori memberi peringkat per kelompok;
-- id sebagai tie-breaker menjaga hasil tetap sama antar eksekusi.
-- Alternatif: LATERAL join per kategori (chapter Lateral Join) saat
-- daftar kategori datang dari tabel lain.
WITH ber_peringkat AS (
    SELECT id,
           pelanggan,
           kategori,
           tanggal,
           jumlah,
           row_number() OVER (PARTITION BY kategori ORDER BY jumlah DESC, id ASC) AS peringkat
    FROM transaksi
)
SELECT kategori,
       peringkat,
       pelanggan,
       tanggal,
       jumlah
FROM ber_peringkat
WHERE peringkat <= 3
ORDER BY kategori, peringkat;

    kategori     | peringkat | pelanggan |  tanggal   |  jumlah   
-----------------+-----------+-----------+------------+-----------
 Buku            |         1 | Joko      | 2025-11-15 | 938400.00
 Buku            |         2 | Andi      | 2025-07-15 | 884400.00
 Buku            |         3 | Andi      | 2025-11-15 | 848600.00
 Elektronik      |         1 | Budi      | 2025-01-15 | 926400.00
 Elektronik      |         2 | Tono      | 2025-09-15 | 872400.00
 Elektronik      |         3 | Tono      | 2025-01-15 | 836600.00
 Fashion         |         1 | Wati      | 2025-10-15 | 948350.00
 Fashion         |         2 | Siti      | 2025-02-15 | 924350.00
 Fashion         |         3 | Siti      | 2025-06-15 | 887150.00
 Peralatan Rumah |         1 | Mira      | 2025-12-15 | 936350.00
 Peralatan Rumah |         2 | Rina      | 2025-04-15 | 912350.00
 Peralatan Rumah |         3 | Mira      | 2025-04-15 | 899150.00
(12 rows)

-- Pola 5: histogram dengan width_bucket. Kapan dipakai: melihat bentuk
-- distribusi (banyak transaksi kecil vs sedikit besar) -- lebih jujur
-- daripada rata-rata saja. Rentang 0..1.000.000 dibagi 4 ember sama
-- lebar; LEFT JOIN ke generate_series menjaga ember kosong tetap tampil.
WITH ber_ember AS (
    SELECT width_bucket(jumlah, 0, 1000000, 4) AS ember
    FROM transaksi
)
SELECT gs.ember,
       count(b.ember) AS jumlah_transaksi,
       round(100.0 * count(b.ember) / sum(count(b.ember)) OVER (), 1) AS persen,
       repeat('#', (count(b.ember) / 4)::integer) AS batang
FROM generate_series(1, 4) AS gs(ember)
LEFT JOIN ber_ember b ON b.ember = gs.ember
GROUP BY gs.ember
ORDER BY gs.ember;

-- Pola 6: persentil lewat ordered-set aggregate. Kapan dipakai: median
-- tahan terhadap nilai ekstrem -- cocok untuk durasi sesi, nilai belanja,
-- waktu respons. percentile_cont menyisipkan (interpolasi) di antara dua
-- nilai tengah; bandingkan dengan rata-rata untuk membaca kemiringan:
-- rata-rata yang jauh di atas median = ekor kanan yang panjang.
SELECT kategori,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY jumlah) AS median,
       round(avg(jumlah), 2) AS rata_rata,
       count(*) AS transaksi
FROM transaksi
GROUP BY kategori
ORDER BY kategori;

 ember | jumlah_transaksi | persen |        batang         
-------+------------------+--------+-----------------------
     1 |               85 |   42.5 | #####################
     2 |               53 |   26.5 | #############
     3 |               38 |   19.0 | #########
     4 |               24 |   12.0 | ######
(4 rows)

    kategori     | median | rata_rata | transaksi 
-----------------+--------+-----------+-----------
 Buku            | 302500 | 369680.00 |        50
 Elektronik      | 290500 | 370760.00 |        50
 Fashion         | 329950 | 387070.00 |        50
 Peralatan Rumah | 317950 | 386590.00 |        50
(4 rows)

-- Pola 5b: persentil dengan percentile_cont. Kapan dipakai: ringkasan
-- distribusi yang tahan terhadap pencilan -- median (p50) tidak
-- tergeser seberapa pun nilai terbesar melonjak.
SELECT round((percentile_cont(0.5) WITHIN GROUP (ORDER BY jumlah))::numeric, 2) AS median,
       round((percentile_cont(0.9) WITHIN GROUP (ORDER BY jumlah))::numeric, 2) AS persen_90
FROM transaksi;

  median   | persen_90 
-----------+-----------
 306100.00 | 787235.00
(1 row)

-- Pola 7: pivot -- baris kategori/kanel menjadi kolom. Kapan dipakai:
-- laporan matriks (kuartal x kanal) untuk manusia; database tetap
-- menyimpan baris, pivot hanya tampilan akhir.
--
-- Versi 1: pivot manual dengan agregat FILTER -- tiap kolom adalah satu
-- ekspresi FILTER; menambah kolom = menambah satu baris SELECT.
SELECT extract(quarter FROM bulan)::integer AS kuartal,
       round(sum(pendapatan) FILTER (WHERE kanal = 'web')) AS web,
       round(sum(pendapatan) FILTER (WHERE kanal = 'aplikasi')) AS aplikasi,
       round(sum(pendapatan) FILTER (WHERE kanal = 'offline')) AS offline
FROM penjualan_bulanan
WHERE bulan >= DATE '2025-01-01'
GROUP BY 1
ORDER BY 1;

-- Versi 2: crosstab() dari extension tablefunc. Query sumber (argumen
-- pertama) menghasilkan (baris, kategori, nilai); query kategori
-- (argumen kedua) menentukan urutan kolom; nama dan tipe kolom hasil
-- dideklarasikan di AS ct(...). Sel yang pasangannya tidak ada menjadi
-- NULL -- siap dibungkus coalesce bila ingin tampil 0.
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    'SELECT extract(quarter FROM bulan)::integer AS kuartal,
            kanal,
            round(sum(pendapatan))
     FROM penjualan_bulanan
     WHERE bulan >= DATE ''2025-01-01''
     GROUP BY 1, 2
     ORDER BY 1, 2',
    'SELECT DISTINCT kanal FROM penjualan_bulanan ORDER BY kanal'
) AS ct(kuartal integer, aplikasi numeric, offline numeric, web numeric)
ORDER BY kuartal;

 kuartal |   web    | aplikasi | offline  
---------+----------+----------+----------
       1 | 46920000 | 41640000 | 16320000
       2 | 49260000 | 45420000 | 15960000
       3 | 51600000 | 49200000 | 15600000
       4 | 62940000 | 59380000 | 18840000
(4 rows)

CREATE EXTENSION
 kuartal | aplikasi | offline  |   web    
---------+----------+----------+----------
       1 | 41640000 | 16320000 | 46920000
       2 | 45420000 | 15960000 | 49260000
       3 | 49200000 | 15600000 | 51600000
       4 | 59380000 | 18840000 | 62940000
(4 rows)