SQL Mastery for Data-Driven Insights: How I Turned Raw Data into Actionable Business Intelligence

-- Inspect sample data
SELECT * FROM customers LIMIT 10;
SELECT * FROM orders LIMIT 10;
SELECT * FROM marketing_campaigns LIMIT 10;

-- Check for missing values
SELECT
    COUNT(*) AS total_rows,
    COUNT(email) AS email_present,
    COUNT(first_purchase_date) AS purchase_date_present
FROM customers;

SELECT
    c.campaign_id,
    mc.campaign_name,
    COUNT(DISTINCT c.customer_id) AS new_customers,
    SUM(o.order_value) AS total_revenue
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN marketing_campaigns mc
    ON c.campaign_id = mc.campaign_id
WHERE c.first_purchase_date BETWEEN '2025-01-01' AND '2025-06-30'
GROUP BY c.campaign_id, mc.campaign_name
ORDER BY total_revenue DESC;

WITH customer_cohorts AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', first_purchase_date) AS cohort_month,
        order_id,
        order_date,
        order_value
    FROM orders
)
SELECT
    cohort_month,
    DATE_TRUNC('month', order_date) AS order_month,
    COUNT(DISTINCT customer_id) AS active_customers,
    SUM(order_value) AS revenue
FROM customer_cohorts
GROUP BY cohort_month, order_month
ORDER BY cohort_month, order_month;

SELECT
    c.customer_id,
    SUM(o.order_value * 0.10) AS lifetime_profit
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY lifetime_profit DESC
LIMIT 20;

WITH customer_clv AS (
    SELECT
        customer_id,
        SUM(order_value * 0.10) AS lifetime_profit
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    lifetime_profit,
    CASE
        WHEN lifetime_profit >= 200 THEN 'High Value'
        WHEN lifetime_profit BETWEEN 100 AND 199 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS segment
FROM customer_clv
ORDER BY lifetime_profit DESC;

SELECT
    mc.campaign_name,
    DATE_TRUNC('day', o.order_date) AS order_day,
    SUM(o.order_value) AS daily_revenue,
    COUNT(DISTINCT o.customer_id) AS daily_customers
FROM orders o
JOIN marketing_campaigns mc
    ON o.campaign_id = mc.campaign_id
WHERE o.order_date BETWEEN '2025-07-01' AND '2025-09-30'
GROUP BY mc.campaign_name, order_day
ORDER BY order_day, mc.campaign_name;

WITH funnel AS (
    SELECT
        customer_id,
        campaign_id,
        MAX(CASE WHEN action = 'click' THEN 1 ELSE 0 END) AS clicked,
        MAX(CASE WHEN action = 'booking' THEN 1 ELSE 0 END) AS booked
    FROM campaign_events
    GROUP BY customer_id, campaign_id
)
SELECT
    campaign_id,
    SUM(booked) AS total_bookings,
    SUM(clicked) AS total_clicks,
    ROUND(SUM(booked)::decimal / SUM(clicked) * 100, 2) AS conversion_rate
FROM funnel
GROUP BY campaign_id
ORDER BY conversion_rate DESC;

WITH new_users AS (
    SELECT
        c.customer_id,
        c.campaign_id,
        MIN(o.order_date) AS first_order_date
    FROM customers c
    JOIN orders o
        ON c.customer_id = o.customer_id
    WHERE o.order_date >= '2025-01-01'
    GROUP BY c.customer_id, c.campaign_id
),
clv_6_months AS (
    SELECT
        n.campaign_id,
        SUM(o.order_value * 0.10) AS profit_6_months
    FROM new_users n
    JOIN orders o
        ON n.customer_id = o.customer_id
    WHERE o.order_date BETWEEN n.first_order_date AND n.first_order_date + INTERVAL '6 months'
    GROUP BY n.campaign_id
)
SELECT
    mc.campaign_name,
    profit_6_months
FROM clv_6_months c
JOIN marketing_campaigns mc
    ON c.campaign_id = mc.campaign_id
ORDER BY profit_6_months DESC;

WITH orders_summary AS (
    SELECT customer_id, SUM(order_value) AS total_value
    FROM orders
    GROUP BY customer_id
)
SELECT c.customer_id, os.total_value
FROM customers c
JOIN orders_summary os
    ON c.customer_id = os.customer_id;

WITH activity_gap AS (
    SELECT
        customer_id,
        MAX(order_date) AS last_order_date,
        CURRENT_DATE - MAX(order_date) AS days_since_last_order
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    days_since_last_order,
    CASE
        WHEN days_since_last_order > 180 THEN 'High Risk'
        WHEN days_since_last_order BETWEEN 90 AND 180 THEN 'Medium Risk'
        ELSE 'Low Risk'
    END AS churn_risk
FROM activity_gap
ORDER BY days_since_last_order DESC;

WITH campaign_metrics AS (
    SELECT
        o.campaign_id,
        SUM(o.order_value * 0.10) AS total_profit,
        COUNT(DISTINCT o.customer_id) AS total_customers,
        AVG(DATE_PART('day', MAX(o.order_date) - MIN(o.order_date))) AS avg_retention_days
    FROM orders o
    GROUP BY o.campaign_id
),
campaign_costs AS (
    SELECT campaign_id, SUM(spend) AS total_cost
    FROM marketing_spend
    GROUP BY campaign_id
)
SELECT
    mc.campaign_name,
    cm.total_profit,
    cc.total_cost,
    ROUND((cm.total_profit - cc.total_cost) / cc.total_cost * 100, 2) AS roi_percentage,
    cm.avg_retention_days
FROM campaign_metrics cm
JOIN campaign_costs cc
    ON cm.campaign_id = cc.campaign_id
JOIN marketing_campaigns mc
    ON mc.campaign_id = cm.campaign_id
ORDER BY roi_percentage DESC;