Belajar BI Analyst - SQL untuk BI

#Pendahuluan

Setelah di episode 3 kita merancang framework metrik (revenue, profit margin, AOV), pada episode ini kita mengubah definisi tersebut menjadi SQL yang nyata. SQL adalah bahasa utama BI: apa pun tool dashboard yang kalian pakai, pada akhirnya ia menjalankan query untuk mendapatkan angka.

Mengapa SQL tingkat report ini penting? Karena di dunia nyata, 80% waktu BI Analyst dihabiskan untuk menyusun query yang benar, bukan menggeser visual di dashboard. Kalian harus menguasai tiga kemampuan inti: aggregation (meringkas data), window functions (analisis antar-baris), dan CTE (menyusun query kompleks dengan rapi).

#Setup Data Superstore

Pertama, muat dataset Superstore ke PostgreSQL. Buat database dan muat tabel orders:

createdb -h localhost -U postgres bi_lab
psql -h localhost -U postgres -d bi_lab -c \
  "CREATE TABLE orders (order_id text, order_date date, ship_date date, customer_id text, region text, category text, sub_category text, sales numeric, quantity int, profit numeric);"

Lalu impor data CSV Superstore ke tabel orders (misalnya via \copy atau tool seperti DBeaver). Kolom yang kita butuhkan untuk episode ini: order_id, order_date, region, category, sales, quantity, profit.

#Aggregation Dasar untuk Report

#Total per Kategori dan Region

Query pertama yang akan kalian pakai berulang kali — ringkasan agregat dengan GROUP BY:

SELECT
    category,
    region,
    COUNT(DISTINCT order_id) AS total_orders,
    SUM(sales)              AS revenue,
    SUM(profit)             AS profit,
    ROUND(SUM(profit) / NULLIF(SUM(sales), 0) * 100, 2) AS margin_pct
FROM orders
WHERE order_date >= DATE '2024-01-01'
GROUP BY category, region
ORDER BY revenue DESC;

Perhatikan pola penting:

• COUNT(DISTINCT order_id) menghitung transaksi unik, bukan baris — krusial saat tabel mengandung banyak baris per order.
• NULLIF(SUM(sales), 0) mencegah pembagian dengan nol, penyebab error paling umum di query BI.
• WHERE memfilter sebelum agregasi — jauh lebih efisien daripada memfilter setelah GROUP BY dengan HAVING.

#Pivot Bulanan dengan DATE_TRUNC

Untuk tren bulanan, potong tanggal ke level bulan menggunakan DATE_TRUNC:

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(sales)                      AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

Pola ini adalah fondasi semua line chart tren di dashboard kalian.

#Window Functions untuk Analisis Antar-Baris

Aggregation meringkas beberapa baris menjadi satu; window functions menghitung nilai per baris sambil melihat baris lain di "window". Tiga yang paling sering dipakai BI:

#Running Total (Cumulative)

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(sales)                      AS revenue,
    SUM(SUM(sales)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS running_revenue
FROM orders
GROUP BY 1
ORDER BY 1;

SUM(...) OVER (ORDER BY ...) membangun kumulatif seiring urutan — pola umum untuk grafik "area kumulatif" yang sering diminta stakeholder.

#Moving Average untuk Menghaluskan Tren

WITH monthly AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(sales) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    ROUND(AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma_3m
FROM monthly
ORDER BY month;

Moving average menghilangkan noise musiman dan menampilkan tren yang lebih jelas — teknik yang akan kembali dipakai untuk forecast di episode 15.

#Ranking dengan ROW_NUMBER dan RANK

SELECT *
FROM (
    SELECT
        region,
        category,
        SUM(sales) AS revenue,
        RANK() OVER (PARTITION BY region ORDER BY SUM(sales) DESC) AS rn
    FROM orders
    GROUP BY region, category
) ranked
WHERE rn <= 5;

RANK() OVER (PARTITION BY ... ORDER BY ...) memberi peringkat per grup. Ini pola "top N per grup" yang sangat sering diminta stakeholder — misalnya "5 produk terlaris per region".

#CTE untuk Query Report yang Rapi

CTE (Common Table Expression) dengan WITH memecah query kompleks menjadi langkah yang bisa dibaca. Bandingkan dua gaya — tanpa CTE (berbelit) dan dengan CTE (berurutan):

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        region,
        SUM(sales) AS revenue
    FROM orders
    GROUP BY 1, 2
),
monthly_rank AS (
    SELECT
        month,
        region,
        revenue,
        RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS rn
    FROM monthly
)
SELECT
    month,
    region,
    revenue,
    rn
FROM monthly_rank
WHERE rn = 1
ORDER BY month;

Setiap CTE adalah satu langkah logis: monthly meringkas, monthly_rank memberi peringkat, query akhir memilih pemenang per bulan. Ini membangun query selangkah demi selangkah — jauh lebih mudah dipelihara daripada satu SELECT raksasa.

#Common Pitfalls SQL untuk BI

• COUNT(*) untuk menghitung order: kalau satu order punya banyak baris, hasilnya salah — selalu COUNT(DISTINCT order_id).
• Pembagian integer: SUM(profit) / SUM(sales) di beberapa database menghasilkan integer. Gunakan * 100.0 atau NULLIF seperti contoh di atas.
• Memfilter hasil agregasi dengan WHERE: filter agregat harus HAVING; WHERE hanya untuk kolom mentah.
• Timestamp tanpa timezone: untuk report lintas negara, selalu simpan timestamp with time zone dan konversi di query.
• Tanpa ORDER BY: output BI yang tidak terurut membuat uji kebenaran query jadi sulit.

#Praktik: Bangun Report Pertama

Sekarang gabungkan semuanya — bangun query report utama yang akan menjadi sumber dashboard pertama kalian:

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        COUNT(DISTINCT order_id)        AS total_orders,
        SUM(sales)                      AS revenue,
        SUM(profit)                     AS profit
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    total_orders,
    revenue,
    profit,
    ROUND(revenue / NULLIF(total_orders, 0), 2)                    AS avg_order_value,
    ROUND(profit / NULLIF(revenue, 0) * 100, 2)                     AS margin_pct,
    ROUND(SUM(revenue) OVER (ORDER BY month), 2)                    AS running_revenue
FROM monthly
ORDER BY month;

Simpan hasilnya (atau jadikan view di PostgreSQL). Report inilah yang nanti akan kalian muat ke Power BI/Tableau/Looker mulai episode 8.

#Penutup

Inti yang harus dibawa pulang:

• Aggregation (GROUP BY + DATE_TRUNC) adalah fondasi semua angka report.
• Window functions (SUM() OVER, AVG() OVER, RANK() OVER) membuka analisis antar-baris: running total, moving average, top-N.
• CTE menyusun query kompleks menjadi langkah logis yang mudah dipelihara.
• Hindari pitfall klasik: COUNT(DISTINCT), NULLIF, HAVING, dan timezone.
• Praktik: report bulanan Superstore siap dipakai di episode berikutnya.

Di episode 5 selanjutnya kita akan membahas data modeling untuk BI — star schema, fact & dimension tables, dan bagaimana merancang model yang membuat query report ini lebih cepat dan lebih mudah. Pastikan report bulanan tadi tersimpan, karena kita akan membangun model di atasnya!