Olist Intelligence — Advanced SQL Analytics (Personal Project)

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
SET AUTOCOMMIT=0;

LOAD DATA LOCAL INFILE 'olist_orders_dataset.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

COMMIT;
SET AUTOCOMMIT=1;
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;

-- Orphan order_items (no matching order)
SELECT COUNT(*) AS orphan_order_items
FROM order_items oi 
LEFT JOIN orders o ON o.order_id = oi.order_id
WHERE o.order_id IS NULL;

-- Orphan orders (no matching customer)
SELECT COUNT(*) AS orphan_orders_customers
FROM orders o 
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

-- Step 1: Add new DATETIME columns
ALTER TABLE orders
  ADD COLUMN order_purchase_dt DATETIME NULL;

-- Step 2: Populate with parsed values
UPDATE orders
SET order_purchase_dt = STR_TO_DATE(
  order_purchase_timestamp, '%Y-%m-%d %H:%i:%s'
);

-- Step 3: Validate conversion (should be 0)
SELECT SUM(
  order_purchase_timestamp IS NOT NULL 
  AND order_purchase_dt IS NULL
) AS bad_parse;

-- Step 4: Swap columns
ALTER TABLE orders
  DROP COLUMN order_purchase_timestamp,
  RENAME COLUMN order_purchase_dt TO order_purchase_timestamp;

customers(customer_id)
orders(order_id)
products(product_id)
sellers(seller_id)

order_items(order_id, order_item_id)
order_payments(order_id, payment_sequential)
order_reviews(order_id, review_id)

orders → customers
order_items → orders / products / sellers
order_payments → orders
order_reviews → orders

CREATE OR REPLACE VIEW vw_orders_enriched AS
SELECT
    o.order_id,
    o.customer_id,
    o.order_status,
    o.order_purchase_timestamp,
    o.order_delivered_customer_date,
    o.order_estimated_delivery_date,
    c.customer_unique_id,
    c.customer_city,
    c.customer_state
FROM orders o
LEFT JOIN customers c
  ON c.customer_id = o.customer_id;

CREATE OR REPLACE VIEW vw_order_items_enriched AS
SELECT
    oi.order_id,
    oi.order_item_id,
    oi.product_id,
    oi.seller_id,
    oi.price,
    oi.freight_value,
    p.product_category_name,
    s.seller_city,
    s.seller_state
FROM order_items oi
LEFT JOIN products p ON p.product_id = oi.product_id
LEFT JOIN sellers s ON s.seller_id = oi.seller_id;

orders(customer_id)              -- customer lookup
orders(order_purchase_timestamp) -- time-series queries
order_items(order_id)            -- order-item joins
order_items(product_id)          -- product analysis
order_payments(order_id)         -- payment aggregation
order_reviews(order_id)          -- review correlation

SELECT
    DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') AS ym,
    AVG(t.order_gmv) AS avg_order_value
FROM orders o
JOIN (
    SELECT order_id, SUM(price + freight_value) AS order_gmv
    FROM order_items
    GROUP BY order_id
) t ON t.order_id = o.order_id
GROUP BY ym
ORDER BY ym;

SELECT 
    c.customer_state,
    COUNT(*) AS delivered_orders,
    SUM(o.order_delivered_customer_date > 
        o.order_estimated_delivery_date) AS late_deliveries,
    ROUND(100 * SUM(o.order_delivered_customer_date > 
        o.order_estimated_delivery_date) / COUNT(*), 2) AS late_rate_pct
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_delivered_customer_date IS NOT NULL
  AND o.order_estimated_delivery_date IS NOT NULL
GROUP BY c.customer_state
HAVING COUNT(*) >= 200
ORDER BY late_rate_pct DESC;

WITH first_purchase AS (
    SELECT
        c.customer_unique_id,
        MIN(DATE(o.order_purchase_timestamp)) AS first_order_date
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
    GROUP BY c.customer_unique_id
),

cohorts AS (
    SELECT
        customer_unique_id,
        DATE_FORMAT(first_order_date, '%Y-%m') AS cohort_ym
    FROM first_purchase
),

cohort_size AS (
    SELECT cohort_ym, COUNT(DISTINCT customer_unique_id) AS cohort_customers
    FROM cohorts
    GROUP BY cohort_ym
),

cohort_activity AS (
    SELECT
        ch.cohort_ym,
        PERIOD_DIFF(
            REPLACE(DATE_FORMAT(o.order_purchase_timestamp,'%Y-%m'),'-',''),
            REPLACE(ch.cohort_ym,'-','')
        ) AS month_index,
        COUNT(DISTINCT c.customer_unique_id) AS active_customers
    FROM cohorts ch
    JOIN customers c ON c.customer_unique_id = ch.customer_unique_id
    JOIN orders o ON o.customer_id = c.customer_id
    GROUP BY ch.cohort_ym, month_index
)

SELECT
    ca.cohort_ym,
    ca.month_index,
    ca.active_customers,
    cs.cohort_customers,
    ROUND(100 * ca.active_customers / cs.cohort_customers, 2) AS retention_pct
FROM cohort_activity ca
JOIN cohort_size cs USING (cohort_ym)
WHERE ca.month_index >= 0
ORDER BY ca.cohort_ym, ca.month_index;