Exploratory Data Analysis (EDA) dengan SQL: Template Lengkap

Apa Itu EDA dan Kenapa Penting?

"Kenal dulu, baru analisis."

Itu prinsip dasar Exploratory Data Analysis (EDA). Sebelum kamu bikin model, dashboard, atau insight apapun, kamu harus kenal dulu sama datanya.

EDA itu proses investigasi awal untuk memahami karakteristik data. Kamu bakal nyari jawaban dari pertanyaan kayak:
- Data ini strukturnya gimana?
- Ada berapa banyak records?
- Missing values-nya banyak ga?
- Distribusinya kayak gimana?
- Ada outliers aneh ga?

Kenapa ini penting banget? Karena tanpa EDA, kamu bisa:
- Bikin kesimpulan yang salah
- Miss insight penting
- Buang waktu debug error yang harusnya bisa dihindari

Di bawah ada template EDA lengkap yang bisa langsung kamu pake untuk project apapun.

Apa yang Akan Kamu Pelajari

• [ ] Step 1: Mengenal struktur data
• [ ] Step 2: Summary statistics
• [ ] Step 3: Cek missing values dan completeness
• [ ] Step 4: Distribusi data
• [ ] Step 5: Cek outliers
• [ ] Step 6: Cardinality check
• [ ] Step 7: Korelasi antar variabel
• [ ] Step 8: Time-series patterns
• [ ] Template EDA yang reusable

Dataset yang Akan Kita Pakai

Ceritanya kamu baru gabung sebagai Data Analyst di startup e-commerce Indonesia. Kamu dapet akses ke database dan diminta untuk "explore" data transaksi.

Tabel: orders

Data ini sengaja dibikin "kotor" biar lebih realistis.

Step 1: Mengenal Struktur Data

Cek Jumlah Total Records

SELECT COUNT(*) AS total_records
FROM orders;

Hasil:

Cek Kolom dan Tipe Data (PostgreSQL)

SELECT
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Quick Sample Data

SELECT *
FROM orders
LIMIT 5;

Selalu liat sample data dulu. Kadang ada anomali yang langsung keliatan dari sample.

Cek Range Tanggal

SELECT
    MIN(order_date) AS earliest_date,
    MAX(order_date) AS latest_date,
    MAX(order_date) - MIN(order_date) AS date_range_days,
    COUNT(DISTINCT DATE(order_date)) AS unique_dates
FROM orders;

Hasil:

Data spanning 8 hari dengan 9 tanggal unik.

Step 2: Summary Statistics

Numeric Columns

SELECT
    'quantity' AS column_name,
    COUNT(*) AS total,
    COUNT(quantity) AS non_null,
    COUNT(*) - COUNT(quantity) AS null_count,
    ROUND(AVG(quantity), 2) AS mean,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY quantity) AS median,
    MODE() WITHIN GROUP (ORDER BY quantity) AS mode,
    MIN(quantity) AS min,
    MAX(quantity) AS max,
    ROUND(STDDEV(quantity), 2) AS std_dev
FROM orders

UNION ALL

SELECT
    'unit_price',
    COUNT(*),
    COUNT(unit_price),
    COUNT(*) - COUNT(unit_price),
    ROUND(AVG(unit_price), 2),
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY unit_price),
    MODE() WITHIN GROUP (ORDER BY unit_price),
    MIN(unit_price),
    MAX(unit_price),
    ROUND(STDDEV(unit_price), 2)
FROM orders

UNION ALL

SELECT
    'total_amount',
    COUNT(*),
    COUNT(total_amount),
    COUNT(*) - COUNT(total_amount),
    ROUND(AVG(total_amount), 2),
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount),
    MODE() WITHIN GROUP (ORDER BY total_amount),
    MIN(total_amount),
    MAX(total_amount),
    ROUND(STDDEV(total_amount), 2)
FROM orders;

Hasil:

Insight:
- Ada 1 NULL di quantity
- unit_price range-nya gede banget (15rb - 15jt), standard deviation tinggi
- Mean jauh dari median = distribusi skewed

Step 3: Cek Missing Values

Missing Value Count per Kolom

SELECT
    COUNT(*) AS total_rows,
    COUNT(*) - COUNT(order_id) AS null_order_id,
    COUNT(*) - COUNT(customer_id) AS null_customer_id,
    COUNT(*) - COUNT(order_date) AS null_order_date,
    COUNT(*) - COUNT(product_category) AS null_category,
    COUNT(*) - COUNT(quantity) AS null_quantity,
    COUNT(*) - COUNT(unit_price) AS null_unit_price,
    COUNT(*) - COUNT(total_amount) AS null_total,
    COUNT(*) - COUNT(payment_method) AS null_payment,
    COUNT(*) - COUNT(status) AS null_status,
    COUNT(*) - COUNT(city) AS null_city
FROM orders;

Hasil:

Insight: Ada missing values di customer_id, quantity, dan city.

Missing Value Percentage

SELECT
    ROUND(100.0 * (COUNT(*) - COUNT(customer_id)) / COUNT(*), 2) AS pct_null_customer_id,
    ROUND(100.0 * (COUNT(*) - COUNT(quantity)) / COUNT(*), 2) AS pct_null_quantity,
    ROUND(100.0 * (COUNT(*) - COUNT(city)) / COUNT(*), 2) AS pct_null_city
FROM orders;

Hasil:

10% missing rate cukup tinggi, perlu diinvestigasi.

Rows with Any NULL

SELECT *
FROM orders
WHERE customer_id IS NULL
   OR quantity IS NULL
   OR city IS NULL;

Step 4: Distribusi Data

Categorical Columns Distribution

-- Product Category Distribution
SELECT
    product_category,
    COUNT(*) AS count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM orders
GROUP BY product_category
ORDER BY count DESC;

Hasil:

-- Payment Method Distribution
SELECT
    payment_method,
    COUNT(*) AS count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM orders
GROUP BY payment_method
ORDER BY count DESC;

Hasil:

-- Status Distribution
SELECT
    status,
    COUNT(*) AS count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM orders
GROUP BY status
ORDER BY count DESC;

Hasil:

Numeric Distribution (Histogram Buckets)

WITH buckets AS (
    SELECT
        CASE
            WHEN total_amount < 500000 THEN '< 500K'
            WHEN total_amount < 1000000 THEN '500K - 1M'
            WHEN total_amount < 5000000 THEN '1M - 5M'
            WHEN total_amount < 10000000 THEN '5M - 10M'
            ELSE '>= 10M'
        END AS amount_bucket,
        CASE
            WHEN total_amount < 500000 THEN 1
            WHEN total_amount < 1000000 THEN 2
            WHEN total_amount < 5000000 THEN 3
            WHEN total_amount < 10000000 THEN 4
            ELSE 5
        END AS bucket_order
    FROM orders
)
SELECT
    amount_bucket,
    COUNT(*) AS count,
    REPEAT('█', COUNT(*)) AS visual
FROM buckets
GROUP BY amount_bucket, bucket_order
ORDER BY bucket_order;

Hasil:

Kebanyakan transaksi di bawah 500K, tapi ada beberapa high-value transactions.

Step 5: Cek Outliers

Statistik untuk Deteksi Outliers

WITH stats AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS q1,
        PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_amount) AS median,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) AS q3
    FROM orders
)
SELECT
    q1,
    median,
    q3,
    q3 - q1 AS iqr,
    q1 - 1.5 * (q3 - q1) AS lower_bound,
    q3 + 1.5 * (q3 - q1) AS upper_bound
FROM stats;

Identifikasi Outliers

WITH stats AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) AS q3
    FROM orders
)
SELECT
    o.*,
    CASE
        WHEN o.total_amount < (s.q1 - 1.5 * (s.q3 - s.q1)) THEN 'Low Outlier'
        WHEN o.total_amount > (s.q3 + 1.5 * (s.q3 - s.q1)) THEN 'High Outlier'
        ELSE 'Normal'
    END AS outlier_status
FROM orders o
CROSS JOIN stats s
WHERE o.total_amount < (s.q1 - 1.5 * (s.q3 - s.q1))
   OR o.total_amount > (s.q3 + 1.5 * (s.q3 - s.q1));

Cek Nilai Extreme

-- Top 5 highest
SELECT order_id, customer_id, total_amount, product_category
FROM orders
ORDER BY total_amount DESC
LIMIT 5;

-- Top 5 lowest
SELECT order_id, customer_id, total_amount, product_category
FROM orders
ORDER BY total_amount ASC
LIMIT 5;

Step 6: Cardinality Check

Cardinality = jumlah nilai unik dalam kolom.

SELECT
    COUNT(DISTINCT order_id) AS unique_order_id,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(DISTINCT product_category) AS unique_categories,
    COUNT(DISTINCT payment_method) AS unique_payment_methods,
    COUNT(DISTINCT status) AS unique_status,
    COUNT(DISTINCT city) AS unique_cities,
    COUNT(DISTINCT DATE(order_date)) AS unique_dates
FROM orders;

Hasil:

Insight:
- 10 orders dari 7 unique customers (ada repeat buyers)
- 4 kategori, 4 payment methods, 3 status
- 5 kota berbeda

High Cardinality Check

Untuk kolom yang harusnya low cardinality tapi ternyata high:

SELECT
    city,
    COUNT(*) AS count
FROM orders
WHERE city IS NOT NULL
GROUP BY city
ORDER BY city;

Cek apakah ada typo atau variasi penulisan (misalnya "Jakarta" vs "JAKARTA" vs "Jkt").

Step 7: Korelasi Antar Variabel

Cross-tabulation (Kategori vs Kategori)

SELECT
    product_category,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY product_category
ORDER BY product_category;

Hasil:

Insight: Makanan punya rate cancelled dan pending yang tinggi relatif terhadap total order-nya.

Numeric by Category

SELECT
    product_category,
    COUNT(*) AS total_orders,
    ROUND(AVG(total_amount), 0) AS avg_amount,
    ROUND(AVG(quantity), 1) AS avg_quantity
FROM orders
WHERE quantity IS NOT NULL
GROUP BY product_category
ORDER BY avg_amount DESC;

Hasil:

Insight: Elektronik = high value, low quantity. Makanan = low value, high quantity.

Step 8: Time-Series Patterns

Daily Trends

SELECT
    DATE(order_date) AS date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS daily_revenue,
    ROUND(AVG(total_amount), 0) AS avg_order_value
FROM orders
GROUP BY DATE(order_date)
ORDER BY date;

Hasil:

Day of Week Pattern

SELECT
    EXTRACT(DOW FROM order_date) AS day_of_week,
    CASE EXTRACT(DOW FROM order_date)
        WHEN 0 THEN 'Minggu'
        WHEN 1 THEN 'Senin'
        WHEN 2 THEN 'Selasa'
        WHEN 3 THEN 'Rabu'
        WHEN 4 THEN 'Kamis'
        WHEN 5 THEN 'Jumat'
        WHEN 6 THEN 'Sabtu'
    END AS day_name,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue
FROM orders
GROUP BY EXTRACT(DOW FROM order_date)
ORDER BY day_of_week;

Growth Trend

WITH daily AS (
    SELECT
        DATE(order_date) AS date,
        COUNT(*) AS orders,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY DATE(order_date)
)
SELECT
    date,
    orders,
    revenue,
    LAG(orders) OVER (ORDER BY date) AS prev_orders,
    LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
    orders - LAG(orders) OVER (ORDER BY date) AS order_change,
    revenue - LAG(revenue) OVER (ORDER BY date) AS revenue_change
FROM daily
ORDER BY date;

Template EDA yang Reusable

Copy paste template ini dan ganti nama tabel/kolom sesuai kebutuhan:

-- =============================================
-- EDA TEMPLATE
-- Ganti 'your_table' dan nama kolom sesuai data kamu
-- =============================================

-- 1. BASIC INFO
SELECT
    'Total Records' AS metric,
    COUNT(*)::TEXT AS value
FROM your_table

UNION ALL

SELECT
    'Date Range',
    MIN(date_column)::TEXT || ' to ' || MAX(date_column)::TEXT
FROM your_table

UNION ALL

SELECT
    'Unique Primary Key',
    COUNT(DISTINCT primary_key_column)::TEXT
FROM your_table;

-- 2. MISSING VALUES
SELECT
    'Column Name' AS column_name,
    COUNT(*) AS total,
    COUNT(column_name) AS non_null,
    COUNT(*) - COUNT(column_name) AS null_count,
    ROUND(100.0 * (COUNT(*) - COUNT(column_name)) / COUNT(*), 2) AS null_pct
FROM your_table;

-- 3. NUMERIC SUMMARY
SELECT
    'numeric_column' AS column_name,
    COUNT(*) AS count,
    ROUND(AVG(numeric_column), 2) AS mean,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY numeric_column) AS median,
    MIN(numeric_column) AS min,
    MAX(numeric_column) AS max,
    ROUND(STDDEV(numeric_column), 2) AS std_dev
FROM your_table;

-- 4. CATEGORICAL DISTRIBUTION
SELECT
    categorical_column,
    COUNT(*) AS count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM your_table
GROUP BY categorical_column
ORDER BY count DESC;

-- 5. CARDINALITY
SELECT
    COUNT(DISTINCT column1) AS unique_col1,
    COUNT(DISTINCT column2) AS unique_col2,
    COUNT(DISTINCT column3) AS unique_col3
FROM your_table;

-- 6. TIME PATTERNS
SELECT
    DATE_TRUNC('day', date_column) AS date,
    COUNT(*) AS count,
    SUM(amount_column) AS total
FROM your_table
GROUP BY DATE_TRUNC('day', date_column)
ORDER BY date;

Common Mistakes yang Harus Dihindari

Mistake 1: Skip EDA, Langsung Analisis

-- LANGSUNG bikin dashboard tanpa EDA
SELECT category, SUM(amount) FROM orders GROUP BY category;
-- Tapi ga tau ada NULL, outliers, atau data quality issues

Selalu EDA dulu!

Mistake 2: Ignore Missing Values

-- NULL di-ignore, hasilnya bisa misleading
SELECT AVG(quantity) FROM orders;  -- NULL ga dihitung

Mistake 3: Ga Cek Outliers

Satu transaksi 15 juta bisa skew average dari 500rb jadi 2.8 juta.

Mistake 4: Assume Data Quality

Jangan assume. Verify!

Dokumentasi Findings

Setelah EDA, selalu dokumentasikan findings:

## EDA Summary: Orders Table

### Overview
- Total records: 10
- Date range: 2024-01-15 to 2024-01-23
- Unique customers: 7

### Data Quality Issues
1. Missing customer_id: 10% (1 record)
2. Missing quantity: 10% (1 record)
3. Missing city: 10% (1 record)

### Key Distributions
- Product Category: Elektronik (40%), Fashion (30%), Makanan (20%), Beauty (10%)
- Payment: Credit Card (40%), E-Wallet (30%), Bank Transfer (20%), COD (10%)
- Status: Completed (80%), Pending (10%), Cancelled (10%)

### Outliers
- High value transactions: Order #6 (15M), Order #9 (8M)
- These are valid (Elektronik category)

### Recommendations
1. Investigate missing customer_id on order #9
2. High cancellation rate on Makanan category needs attention
3. Consider capping outliers for aggregate analysis

Latihan

Soal: Lakukan EDA lengkap pada tabel orders dan jawab:
1. Berapa completion rate overall?
2. Payment method mana yang punya highest average order value?
3. Ada anomali apa yang perlu diinvestigasi?

-- 1. Completion Rate
SELECT
    ROUND(100.0 * COUNT(CASE WHEN status = 'completed' THEN 1 END) / COUNT(*), 2) AS completion_rate
FROM orders;
-- Hasil: 80%

-- 2. AOV by Payment Method
SELECT
    payment_method,
    COUNT(*) AS orders,
    ROUND(AVG(total_amount), 0) AS avg_order_value
FROM orders
GROUP BY payment_method
ORDER BY avg_order_value DESC;
-- Hasil: credit_card punya highest AOV

-- 3. Anomali
-- a) Order #9 ga punya customer_id
-- b) Order #5 ga punya quantity dan city
-- c) Makanan punya cancellation rate 50%
SELECT * FROM orders WHERE customer_id IS NULL OR quantity IS NULL;

FAQ

Apa bedanya EDA pakai SQL sama pakai Python?

Python (pandas) lebih enak buat visualisasi dan analisis statistik lanjutan. Tapi SQL jalan langsung di database. Kamu nggak perlu export data dulu. Buat dataset jutaan baris, SQL jauh lebih cepat soalnya komputasinya di server, bukan di laptop kamu. Praktiknya banyak analyst pakai dua-duanya: SQL buat first pass, Python buat deep dive. Template di artikel ini udah cukup buat 80% kebutuhan EDA harian kok.

Query di artikel ini jalan di MySQL nggak?

Sebagian besar jalan. Yang perlu disesuaikan: PERCENTILE_CONT dan MODE() itu fitur PostgreSQL. Di MySQL kamu bisa pakai window function (MySQL 8+) atau kombinasi ORDER BY dan LIMIT buat cari median. information_schema.columns sama-sama ada di MySQL. Sisanya kayak COUNT, GROUP BY, dan CASE itu standar SQL, jalan di mana aja.

Berapa lama idealnya ngerjain EDA?

Tergantung ukuran datanya. Buat satu tabel kayak contoh di atas, 30-60 menit udah cukup buat jalanin 8 step ini. Dataset dengan 10+ tabel bisa makan 1-2 hari. Patokannya gini: berhenti kalau kamu udah bisa jawab struktur, kualitas, distribusi, dan anomali datanya. EDA kelamaan tanpa arah itu procrastination terselubung lho.

Kalau nemu missing values, harus diapain?

Jangan langsung dihapus atau diisi. Investigasi dulu kenapa bisa kosong. Bug di aplikasi, data lama, atau memang field opsional? Kalau missing rate di bawah 5% dan polanya random, biasanya aman di-exclude. Kalau polanya sistematis, misalnya semua NULL datang dari satu payment method, itu sinyal masalah di pipeline datanya. Detailnya ada di artikel data cleaning.

Kesimpulan

EDA itu langkah pertama dari setiap analisis yang bener. Inget poin-poin ini:

1. Selalu mulai dengan EDA sebelum analisis apapun
2. Cek struktur data: rows, columns, data types
3. Hitung summary statistics: mean, median, min, max
4. Identifikasi missing values dan persentasenya
5. Pahami distribusi categorical dan numeric
6. Deteksi outliers yang bisa skew analisis
7. Check cardinality untuk potensi data quality issues
8. Explore time patterns kalau ada date column
9. Dokumentasikan semua findings

Template EDA yang reusable bakal bikin hidup kamu lebih gampang. Invest time di awal, save time later!

Happy exploring!

Selanjutnya

Kalau kamu udah paham EDA, next step-nya:
- Data Cleaning - bersihin data yang kotor
- Funnel Analysis - analisis conversion
- Cohort Analysis - analisis retention