SQL Advanced untuk Data Engineering

Sebagai data engineer, SQL bukan hanya SELECT-WHERE-JOIN. Kamu butuh window functions, CTEs, dan analytical queries untuk transformasi data yang kompleks.

CTE (Common Table Expressions)

CTE membuat query lebih readable dengan memecah logic menjadi langkah-langkah.

-- Tanpa CTE: nested subquery susah dibaca
SELECT * FROM (
    SELECT user_id, SUM(amount) as total
    FROM orders GROUP BY user_id
) sub WHERE total > 1000000;

-- Dengan CTE: jelas dan bertahap
WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total_spend
    FROM orders
    GROUP BY user_id
),
high_value AS (
    SELECT * FROM user_totals
    WHERE total_spend > 1000000
)
SELECT u.name, h.total_spend
FROM high_value h
JOIN users u ON u.id = h.user_id
ORDER BY h.total_spend DESC;

Window Functions

Menghitung agregasi tanpa menghilangkan baris — sangat powerful untuk analitik.

-- ROW_NUMBER: ranking per group
SELECT
    user_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_sequence
FROM orders;

-- Running total (kumulatif)
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Moving average (rata-rata 7 hari terakhir)
SELECT
    date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_metrics;

-- LAG / LEAD: akses baris sebelum/sesudah
SELECT
    date,
    daily_revenue,
    LAG(daily_revenue, 1) OVER (ORDER BY date) AS prev_day,
    daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY date) AS day_over_day_change
FROM daily_metrics;

Analytical Patterns

-- Cohort analysis: kapan user pertama kali order?
WITH first_order AS (
    SELECT user_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
    FROM orders GROUP BY user_id
)
SELECT
    f.cohort_month,
    DATE_TRUNC('month', o.order_date) AS activity_month,
    COUNT(DISTINCT o.user_id) AS active_users
FROM orders o
JOIN first_order f ON f.user_id = o.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

-- NTILE: bagi data jadi bucket (quartile, decile)
SELECT
    user_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM user_summary;