12-advanced-analytics.sql

-- ============================================================
-- SQL Masterclass — Chapter 12: Advanced Analytics
-- ============================================================
-- 🔴 ADVANCED — Data Analyst / Data Engineer Level
--
-- In this chapter you will learn:
--   • Cohort analysis (customer retention)
--   • Customer Lifetime Value (CLV) estimation
--   • Funnel analysis (conversion rates)
--   • RFM segmentation (Recency, Frequency, Monetary)
--   • Pivot tables with CASE + GROUP BY
--   • Multi-CTE analytical pipelines
--   • Percentile analysis
--   • Year-over-Year (YoY) growth
-- ============================================================


-- ============================================================
-- 12.1  COHORT ANALYSIS — When did customers first buy?
-- ============================================================
-- A cohort is a group of customers who share a common starting point.

-- Step 1: Find each customer's first purchase month (cohort)
WITH customer_cohort AS (
    SELECT
        customer_id,
        MIN(SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7)) AS cohort_month
    FROM orders
    GROUP BY customer_id
)
SELECT
    cohort_month,
    COUNT(*) AS cohort_size
FROM customer_cohort
GROUP BY cohort_month
ORDER BY cohort_month;


-- Step 2: Full cohort analysis with retention
WITH customer_cohort AS (
    SELECT
        o.customer_id,
        MIN(SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7)) AS cohort_month
    FROM orders o
    GROUP BY o.customer_id
),
customer_orders AS (
    SELECT
        o.customer_id,
        SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS order_month
    FROM orders o
),
cohort_data AS (
    SELECT
        cc.cohort_month,
        co.order_month,
        COUNT(DISTINCT co.customer_id) AS num_customers
    FROM customer_cohort cc
    JOIN customer_orders co ON cc.customer_id = co.customer_id
    GROUP BY cc.cohort_month, co.order_month
)
SELECT
    cohort_month,
    order_month,
    num_customers
FROM cohort_data
WHERE cohort_month >= '2017-06'
  AND cohort_month <= '2017-12'
ORDER BY cohort_month, order_month;


-- ============================================================
-- 12.2  RFM SEGMENTATION
-- ============================================================
-- Recency: How recently did the customer buy?
-- Frequency: How often do they buy?
-- Monetary: How much do they spend?

WITH rfm_base AS (
    SELECT
        c.customer_unique_id,
        MAX(o.order_purchase_timestamp)                    AS last_purchase,
        COUNT(DISTINCT o.order_id)                         AS frequency,
        SUM(p.payment_value)                               AS monetary
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_payments p ON o.order_id = p.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY c.customer_unique_id
),
rfm_scores AS (
    SELECT
        customer_unique_id,
        last_purchase,
        frequency,
        monetary,
        NTILE(5) OVER (ORDER BY last_purchase)  AS r_score,
        NTILE(5) OVER (ORDER BY frequency)      AS f_score,
        NTILE(5) OVER (ORDER BY monetary)       AS m_score
    FROM rfm_base
)
SELECT
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 4 AND f_score >= 3                   THEN 'Loyal'
        WHEN r_score >= 4                                    THEN 'Recent'
        WHEN r_score >= 3 AND m_score >= 3                   THEN 'Promising'
        WHEN r_score <= 2 AND f_score >= 3                   THEN 'At Risk'
        WHEN r_score <= 2 AND f_score <= 2                   THEN 'Lost'
        ELSE 'Other'
    END AS rfm_segment,
    COUNT(*)               AS num_customers,
    AVG(monetary)          AS avg_monetary,
    AVG(frequency)         AS avg_frequency
FROM rfm_scores
GROUP BY
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 4 AND f_score >= 3                   THEN 'Loyal'
        WHEN r_score >= 4                                    THEN 'Recent'
        WHEN r_score >= 3 AND m_score >= 3                   THEN 'Promising'
        WHEN r_score <= 2 AND f_score >= 3                   THEN 'At Risk'
        WHEN r_score <= 2 AND f_score <= 2                   THEN 'Lost'
        ELSE 'Other'
    END
ORDER BY avg_monetary DESC;


-- ============================================================
-- 12.3  FUNNEL ANALYSIS — Order lifecycle
-- ============================================================

-- Order funnel: How many orders reach each stage?
SELECT
    COUNT(*)                                                    AS total_orders,
    SUM(CASE WHEN order_approved_at IS NOT NULL
        THEN 1 ELSE 0 END)                                     AS approved,
    SUM(CASE WHEN order_delivered_carrier_date IS NOT NULL
        THEN 1 ELSE 0 END)                                     AS shipped,
    SUM(CASE WHEN order_delivered_customer_date IS NOT NULL
        THEN 1 ELSE 0 END)                                     AS delivered,
    ROUND(100.0 * SUM(CASE WHEN order_approved_at IS NOT NULL
        THEN 1 ELSE 0 END) / COUNT(*), 1)                      AS approval_rate,
    ROUND(100.0 * SUM(CASE WHEN order_delivered_customer_date IS NOT NULL
        THEN 1 ELSE 0 END) / COUNT(*), 1)                      AS delivery_rate
FROM orders;


-- Funnel by month
SELECT
    SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7)                     AS year_month,
    COUNT(*)                                                    AS total_orders,
    SUM(CASE WHEN order_status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
    SUM(CASE WHEN order_status = 'canceled'  THEN 1 ELSE 0 END) AS canceled,
    ROUND(100.0 * SUM(CASE WHEN order_status = 'canceled' THEN 1 ELSE 0 END)
        / COUNT(*), 2) AS cancel_rate
FROM orders
GROUP BY SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7)
ORDER BY year_month;


-- ============================================================
-- 12.4  CUSTOMER LIFETIME VALUE (CLV) ESTIMATION
-- ============================================================

WITH customer_metrics AS (
    SELECT
        c.customer_unique_id,
        COUNT(DISTINCT o.order_id) AS total_orders,
        SUM(p.payment_value)       AS total_revenue,
        MIN(o.order_purchase_timestamp) AS first_purchase,
        MAX(o.order_purchase_timestamp) AS last_purchase,
        CAST(MAX(o.order_purchase_timestamp) AS DATE) -
        CAST(MIN(o.order_purchase_timestamp) AS DATE) AS customer_lifespan_days
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_payments p ON o.order_id = p.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY c.customer_unique_id
),
clv_metrics AS (
    SELECT
        CASE
            WHEN total_orders = 1 THEN 'One-time'
            WHEN total_orders = 2 THEN 'Repeat (2x)'
            ELSE 'Loyal (3x+)'
        END AS customer_type,
        COUNT(*)                     AS num_customers,
        AVG(total_revenue)           AS avg_revenue,
        AVG(total_orders)            AS avg_orders,
        AVG(customer_lifespan_days)  AS avg_lifespan_days
    FROM customer_metrics
    GROUP BY
        CASE
            WHEN total_orders = 1 THEN 'One-time'
            WHEN total_orders = 2 THEN 'Repeat (2x)'
            ELSE 'Loyal (3x+)'
        END
)
SELECT
    customer_type,
    num_customers,
    ROUND(avg_revenue, 2)       AS avg_revenue,
    ROUND(avg_orders, 2)        AS avg_orders,
    ROUND(avg_lifespan_days, 0) AS avg_lifespan_days
FROM clv_metrics
ORDER BY avg_revenue DESC;


-- ============================================================
-- 12.5  YEAR-OVER-YEAR (YoY) GROWTH
-- ============================================================

WITH yearly_revenue AS (
    SELECT
        SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 4) AS year,
        SUM(oi.price)                             AS revenue,
        COUNT(DISTINCT o.order_id)                AS num_orders
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 4)
)
SELECT
    year,
    revenue,
    num_orders,
    LAG(revenue) OVER (ORDER BY year) AS prev_year_revenue,
    CASE
        WHEN LAG(revenue) OVER (ORDER BY year) > 0
        THEN ROUND(
            100.0 * (revenue - LAG(revenue) OVER (ORDER BY year))
            / LAG(revenue) OVER (ORDER BY year), 1
        )
    END AS yoy_revenue_growth_pct,
    CASE
        WHEN LAG(num_orders) OVER (ORDER BY year) > 0
        THEN ROUND(
            100.0 * (num_orders - LAG(num_orders) OVER (ORDER BY year))
            / LAG(num_orders) OVER (ORDER BY year), 1
        )
    END AS yoy_orders_growth_pct
FROM yearly_revenue
ORDER BY year;


-- ============================================================
-- 12.6  SELLER PERFORMANCE SCORECARD
-- ============================================================
-- Multi-dimensional seller analysis

WITH seller_metrics AS (
    SELECT
        s.seller_id,
        s.seller_city,
        s.seller_state,
        COUNT(DISTINCT oi.order_id)  AS total_orders,
        SUM(oi.price)                AS total_revenue,
        AVG(oi.price)                AS avg_item_price,
        COUNT(DISTINCT oi.product_id) AS unique_products,
        AVG(r.review_score)          AS avg_review_score
    FROM sellers s
    JOIN order_items oi ON s.seller_id = oi.seller_id
    JOIN orders o ON oi.order_id = o.order_id
    LEFT JOIN order_reviews r ON o.order_id = r.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY s.seller_id, s.seller_city, s.seller_state
),
scored AS (
    SELECT
        *,
        NTILE(5) OVER (ORDER BY total_revenue)    AS revenue_score,
        NTILE(5) OVER (ORDER BY total_orders)      AS volume_score,
        NTILE(5) OVER (ORDER BY avg_review_score)  AS quality_score
    FROM seller_metrics
    WHERE total_orders >= 5  -- minimum threshold
)
SELECT
    seller_id,
    seller_state,
    total_orders,
    ROUND(total_revenue, 2)    AS total_revenue,
    ROUND(avg_review_score, 2) AS avg_review,
    revenue_score,
    volume_score,
    quality_score,
    revenue_score + volume_score + quality_score AS composite_score
FROM scored
ORDER BY composite_score DESC
LIMIT 20;


-- ============================================================
-- 12.7  PRODUCT CATEGORY CROSS-SELL ANALYSIS
-- ============================================================
-- Which categories are frequently bought together?

WITH order_categories AS (
    SELECT DISTINCT
        oi.order_id,
        p.product_category_name
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.product_category_name IS NOT NULL
),
category_pairs AS (
    SELECT
        a.product_category_name AS category_a,
        b.product_category_name AS category_b,
        COUNT(DISTINCT a.order_id) AS co_occurrence
    FROM order_categories a
    JOIN order_categories b
        ON a.order_id = b.order_id
        AND a.product_category_name < b.product_category_name
    GROUP BY a.product_category_name, b.product_category_name
)
SELECT *
FROM category_pairs
WHERE co_occurrence > 50
ORDER BY co_occurrence DESC
LIMIT 15;


-- ============================================================
-- 12.8  GEOGRAPHIC MARKET ANALYSIS
-- ============================================================

-- Market penetration: seller coverage vs customer demand
WITH customer_demand AS (
    SELECT
        c.customer_state,
        COUNT(DISTINCT o.order_id) AS total_orders,
        SUM(p.payment_value)       AS total_spend
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_payments p ON o.order_id = p.order_id
    GROUP BY c.customer_state
),
seller_supply AS (
    SELECT
        seller_state,
        COUNT(DISTINCT oi.seller_id)  AS num_sellers,
        SUM(price)                 AS total_supplied
    FROM order_items oi
    JOIN sellers s ON oi.seller_id = s.seller_id
    GROUP BY seller_state
)
SELECT
    d.customer_state,
    d.total_orders,
    ROUND(d.total_spend, 0)    AS customer_spend,
    COALESCE(s.num_sellers, 0) AS local_sellers,
    COALESCE(ROUND(s.total_supplied, 0), 0) AS local_supply,
    CASE
        WHEN COALESCE(s.total_supplied, 0) > 0
        THEN ROUND(d.total_spend / s.total_supplied, 2)
        ELSE NULL
    END AS demand_supply_ratio
FROM customer_demand d
LEFT JOIN seller_supply s ON d.customer_state = s.seller_state
ORDER BY d.total_spend DESC;


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: Build a monthly cohort analysis showing the
--             number of first-time buyers each month for 2018.

-- Exercise 2: Create a "delivery SLA" analysis:
--             For each month, calculate what percentage of
--             orders were delivered within 7, 14, 21, and 30 days.

-- Exercise 3: Build a seller churn analysis:
--             Find sellers who were active in 2017 but had
--             no orders in 2018.

-- Exercise 4: Create an "Average Order Value" (AOV) trend
--             by month with 3-month moving average.


-- ============================================================
-- SOLUTIONS
-- ============================================================

-- Exercise 1
SELECT
    SUBSTR(CAST(first_purchase AS VARCHAR), 1, 7) AS cohort_month,
    COUNT(*) AS new_customers
FROM (
    SELECT
        customer_id,
        MIN(order_purchase_timestamp) AS first_purchase
    FROM orders
    GROUP BY customer_id
)
WHERE SUBSTR(CAST(first_purchase AS VARCHAR), 1, 4) = '2018'
GROUP BY SUBSTR(CAST(first_purchase AS VARCHAR), 1, 7)
ORDER BY cohort_month;

-- Exercise 2
SELECT
    SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
    COUNT(*) AS total_delivered,
    ROUND(100.0 * SUM(CASE
        WHEN CAST(order_delivered_customer_date AS DATE) -
             CAST(order_purchase_timestamp AS DATE) <= 7
        THEN 1 ELSE 0 END) / COUNT(*), 1) AS within_7d,
    ROUND(100.0 * SUM(CASE
        WHEN CAST(order_delivered_customer_date AS DATE) -
             CAST(order_purchase_timestamp AS DATE) <= 14
        THEN 1 ELSE 0 END) / COUNT(*), 1) AS within_14d,
    ROUND(100.0 * SUM(CASE
        WHEN CAST(order_delivered_customer_date AS DATE) -
             CAST(order_purchase_timestamp AS DATE) <= 21
        THEN 1 ELSE 0 END) / COUNT(*), 1) AS within_21d,
    ROUND(100.0 * SUM(CASE
        WHEN CAST(order_delivered_customer_date AS DATE) -
             CAST(order_purchase_timestamp AS DATE) <= 30
        THEN 1 ELSE 0 END) / COUNT(*), 1) AS within_30d
FROM orders
WHERE order_status = 'delivered'
  AND order_delivered_customer_date IS NOT NULL
GROUP BY SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7)
ORDER BY year_month;

-- Exercise 3
WITH sellers_2017 AS (
    SELECT DISTINCT oi.seller_id
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 4) = '2017'
),
sellers_2018 AS (
    SELECT DISTINCT oi.seller_id
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 4) = '2018'
)
SELECT
    s17.seller_id
FROM sellers_2017 s17
LEFT JOIN sellers_2018 s18 ON s17.seller_id = s18.seller_id
WHERE s18.seller_id IS NULL;

-- Exercise 4
WITH monthly_aov AS (
    SELECT
        SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
        AVG(total_payment) AS aov
    FROM (
        SELECT
            o.order_id,
            o.order_purchase_timestamp,
            SUM(p.payment_value) AS total_payment
        FROM orders o
        JOIN order_payments p ON o.order_id = p.order_id
        GROUP BY o.order_id, o.order_purchase_timestamp
    ) o
    GROUP BY SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7)
)
SELECT
    year_month,
    ROUND(aov, 2) AS avg_order_value,
    ROUND(AVG(aov) OVER (
        ORDER BY year_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3m
FROM monthly_aov
ORDER BY year_month;