Real-World SQL Applications

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount) AS revenue,
    ROUND(SUM(amount) / COUNT(DISTINCT customer_id), 2) AS revenue_per_customer
FROM orders
WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE)
  AND status = 'completed'
GROUP BY 1
ORDER BY 1;

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.order_date) AS month,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.status = 'completed'
    GROUP BY 1, 2
)
SELECT
    month,
    category,
    revenue,
    LAG(revenue) OVER (PARTITION BY category ORDER BY month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (PARTITION BY category ORDER BY month))
        / NULLIF(LAG(revenue) OVER (PARTITION BY category ORDER BY month), 0),
        1
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month DESC, revenue DESC;

-- Month 0: how much did each acquisition cohort spend in their first month?
-- Month 1: how much did they spend in month 2? And so on.
WITH cohorts AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
cohort_orders AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', o.order_date) AS order_month,
        COUNT(DISTINCT o.customer_id) AS active_customers,
        SUM(o.amount) AS revenue
    FROM cohorts c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY 1, 2
)
SELECT
    cohort_month,
    order_month,
    EXTRACT(MONTH FROM AGE(order_month, cohort_month)) AS months_since_acquisition,
    active_customers,
    revenue
FROM cohort_orders
ORDER BY cohort_month, months_since_acquisition;

-- Keep the most recent version of each record (CDC dedup pattern)
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM raw_orders
)
SELECT order_id, customer_id, status, amount, created_at, updated_at
FROM ranked
WHERE rn = 1;

-- Find records that changed between yesterday's snapshot and today's
SELECT
    t.customer_id,
    t.email AS new_email,
    y.email AS old_email,
    t.updated_at
FROM customers_today t
JOIN customers_yesterday y ON t.customer_id = y.customer_id
WHERE t.email != y.email
   OR t.status != y.status;

-- Only process records newer than the last successful load
INSERT INTO analytics.orders
SELECT *
FROM raw.orders
WHERE created_at > (
    SELECT COALESCE(MAX(created_at), '1900-01-01') FROM analytics.orders
)
ON CONFLICT (order_id) DO UPDATE
    SET status = EXCLUDED.status,
        updated_at = EXCLUDED.updated_at;

-- Flag accounts with unusual transaction velocity
WITH recent_txns AS (
    SELECT
        account_id,
        COUNT(*) AS txn_count_24h,
        SUM(amount) AS total_amount_24h,
        COUNT(DISTINCT merchant_id) AS unique_merchants_24h
    FROM transactions
    WHERE created_at >= NOW() - INTERVAL '24 hours'
    GROUP BY account_id
)
SELECT
    r.account_id,
    r.txn_count_24h,
    r.total_amount_24h,
    a.average_daily_txns,
    r.txn_count_24h / NULLIF(a.average_daily_txns, 0) AS velocity_ratio
FROM recent_txns r
JOIN account_baselines a ON r.account_id = a.account_id
WHERE r.txn_count_24h > a.average_daily_txns * 5   -- 5x normal velocity
   OR r.total_amount_24h > a.typical_daily_spend * 10;

SELECT
    p.product_id,
    p.name,
    p.sku,
    i.quantity_on_hand,
    p.reorder_point,
    p.reorder_quantity,
    s.name AS primary_supplier
FROM products p
JOIN inventory i ON p.product_id = i.product_id
JOIN suppliers s ON p.primary_supplier_id = s.supplier_id
WHERE i.quantity_on_hand <= p.reorder_point
  AND p.is_active = TRUE
ORDER BY (i.quantity_on_hand::float / NULLIF(p.reorder_point, 0)) ASC;

SELECT
    p.product_id,
    p.name,
    SUM(oi.quantity) AS units_sold_90d,
    AVG(i.quantity_on_hand) AS avg_stock,
    ROUND(SUM(oi.quantity) / NULLIF(AVG(i.quantity_on_hand), 0), 2) AS turnover_ratio
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
JOIN inventory i ON p.product_id = i.product_id
WHERE o.order_date >= CURRENT_DATE - 90
GROUP BY p.product_id, p.name
ORDER BY turnover_ratio DESC;

SELECT
    COUNT(DISTINCT u.user_id) AS signups,
    COUNT(DISTINCT a.user_id) AS activated,
    COUNT(DISTINCT p.customer_id) AS purchased,
    ROUND(100.0 * COUNT(DISTINCT a.user_id) / COUNT(DISTINCT u.user_id), 1) AS activation_rate,
    ROUND(100.0 * COUNT(DISTINCT p.customer_id) / COUNT(DISTINCT a.user_id), 1) AS purchase_rate
FROM users u
LEFT JOIN (
    SELECT DISTINCT user_id FROM events WHERE event_type = 'first_action'
) a ON u.user_id = a.user_id
LEFT JOIN (
    SELECT DISTINCT customer_id FROM orders WHERE status = 'completed'
) p ON u.user_id = p.customer_id
WHERE u.created_at >= '2025-01-01';

WITH day_one AS (
    SELECT user_id, DATE(created_at) AS signup_date FROM users
),
activity AS (
    SELECT DISTINCT user_id, DATE(event_at) AS active_date FROM events
)
SELECT
    d.signup_date,
    COUNT(DISTINCT d.user_id) AS new_users,
    COUNT(DISTINCT CASE WHEN a.active_date = d.signup_date + 1 THEN a.user_id END) AS day_1_retained,
    COUNT(DISTINCT CASE WHEN a.active_date = d.signup_date + 7 THEN a.user_id END) AS day_7_retained
FROM day_one d
LEFT JOIN activity a ON d.user_id = a.user_id
GROUP BY 1
ORDER BY 1 DESC;

SELECT
    COALESCE(region, 'TOTAL') AS region,
    COALESCE(category, 'ALL') AS category,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS customers
FROM sales
GROUP BY ROLLUP (region, category)
ORDER BY region NULLS LAST, category NULLS LAST;