SQL Analysis — E-commerce Analytics

Setup

Load the Olist tables into a database: orders, order_items, customers, order_payments, order_reviews, products (with English category). This project showcases multi-table joins.

---

Q1 — Total Revenue and Orders

SELECT
    COUNT(DISTINCT o.order_id)              AS total_orders,
    COUNT(DISTINCT c.customer_unique_id)    AS unique_customers,
    ROUND(SUM(oi.price + oi.freight_value), 2) AS total_revenue,
    ROUND(SUM(oi.price + oi.freight_value) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN customers c    ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered';

---

Q2 — Revenue by Product Category

SELECT
    p.category,
    COUNT(DISTINCT oi.order_id)             AS orders,
    SUM(oi.price)                           AS revenue,
    ROUND(AVG(oi.price), 2)                 AS avg_item_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o   ON oi.order_id = o.order_id
WHERE o.order_status = 'delivered'
GROUP BY p.category
ORDER BY revenue DESC
LIMIT 15;

---

Q3 — Repeat vs One-Time Customers (Use customer_unique_id!)

WITH customer_orders AS (
    SELECT
        c.customer_unique_id,
        COUNT(DISTINCT o.order_id)          AS order_count
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    WHERE o.order_status = 'delivered'
    GROUP BY c.customer_unique_id
)
SELECT
    CASE WHEN order_count > 1 THEN 'Repeat' ELSE 'One-time' END AS customer_type,
    COUNT(*)                                AS customers,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM customer_orders
GROUP BY CASE WHEN order_count > 1 THEN 'Repeat' ELSE 'One-time' END;

Must use customer_unique_id

Joining on customer_id would show 0% repeat customers — because Olist assigns a new customer_id to every order. Always group repeat analysis by customer_unique_id.

---

Q4 — Delivery Time vs Review Score (Key Relationship)

WITH order_metrics AS (
    SELECT
        o.order_id,
        DATEDIFF(o.order_delivered_customer_date, o.order_purchase_timestamp) AS delivery_days,
        r.review_score
    FROM orders o
    JOIN order_reviews r ON o.order_id = r.order_id
    WHERE o.order_status = 'delivered'
      AND o.order_delivered_customer_date IS NOT NULL
)
SELECT
    CASE
        WHEN delivery_days <= 7  THEN '1. Fast (<=7 days)'
        WHEN delivery_days <= 14 THEN '2. Normal (8-14 days)'
        WHEN delivery_days <= 30 THEN '3. Slow (15-30 days)'
        ELSE '4. Very Slow (30+ days)'
    END                                     AS delivery_speed,
    COUNT(*)                                AS orders,
    ROUND(AVG(review_score), 2)             AS avg_review_score
FROM order_metrics
GROUP BY
    CASE
        WHEN delivery_days <= 7  THEN '1. Fast (<=7 days)'
        WHEN delivery_days <= 14 THEN '2. Normal (8-14 days)'
        WHEN delivery_days <= 30 THEN '3. Slow (15-30 days)'
        ELSE '4. Very Slow (30+ days)'
    END
ORDER BY delivery_speed;

Sample output:

delivery_speed	orders	avg_review_score
1. Fast (<=7 days)	28104	4.32
2. Normal (8-14 days)	41882	4.15
3. Slow (15-30 days)	22663	3.74
4. Very Slow (30+ days)	4521	2.28

Delivery speed directly drives satisfaction

Average review score falls from 4.32 (fast) to 2.28 (very slow). Slow delivery is the clearest driver of bad reviews. This is the single most actionable insight in the dataset.

---

Q5 — Worst-Rated Categories

SELECT
    p.category,
    COUNT(*)                                AS reviewed_orders,
    ROUND(AVG(r.review_score), 2)           AS avg_score,
    SUM(CASE WHEN r.review_score <= 2 THEN 1 ELSE 0 END) AS bad_reviews
FROM order_reviews r
JOIN order_items oi ON r.order_id = oi.order_id
JOIN products p     ON oi.product_id = p.product_id
GROUP BY p.category
HAVING COUNT(*) >= 100
ORDER BY avg_score ASC
LIMIT 10;

---

Q6 — Payment Method Analysis

SELECT
    payment_type,
    COUNT(*)                                AS payments,
    ROUND(AVG(payment_value), 2)            AS avg_payment,
    ROUND(AVG(payment_installments), 1)     AS avg_installments
FROM order_payments
GROUP BY payment_type
ORDER BY payments DESC;

---

Q7 — RFM Segmentation

WITH rfm AS (
    SELECT
        c.customer_unique_id,
        DATEDIFF('2018-10-01', MAX(o.order_purchase_timestamp)) AS recency_days,
        COUNT(DISTINCT o.order_id)          AS frequency,
        SUM(oi.price + oi.freight_value)    AS monetary
    FROM orders o
    JOIN customers c    ON o.customer_id = c.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY c.customer_unique_id
),
scored AS (
    SELECT
        customer_unique_id,
        recency_days, frequency, monetary,
        NTILE(5) OVER (ORDER BY recency_days ASC)  AS r_score,   -- lower recency = better
        NTILE(5) OVER (ORDER BY frequency DESC)    AS f_score,
        NTILE(5) OVER (ORDER BY monetary DESC)     AS m_score
    FROM rfm
)
SELECT
    customer_unique_id, recency_days, frequency, ROUND(monetary, 2) AS monetary,
    r_score, f_score, m_score,
    CASE
        WHEN r_score >= 4 AND f_score >= 4 THEN 'Champions'
        WHEN r_score >= 3 AND f_score >= 2 THEN 'Loyal'
        WHEN r_score >= 4 AND f_score = 1  THEN 'New'
        WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
        ELSE 'Other'
    END                                     AS segment
FROM scored
ORDER BY monetary DESC
LIMIT 20;

---

Q8 — Geographic Revenue (by State)

SELECT
    c.customer_state,
    COUNT(DISTINCT o.order_id)              AS orders,
    ROUND(SUM(oi.price + oi.freight_value), 0) AS revenue,
    ROUND(AVG(DATEDIFF(o.order_delivered_customer_date, o.order_purchase_timestamp)), 1) AS avg_delivery_days
FROM orders o
JOIN customers c    ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL
GROUP BY c.customer_state
ORDER BY revenue DESC
LIMIT 10;

---

← Data Cleaning · Next: Dashboard Design →