analysis_queries.sql

-- BASIC EXPLORATION

-- Total Orders
SELECT COUNT(*) AS total_orders 
FROM olist_orders_dataset;

-- Total Customers
SELECT COUNT(DISTINCT customer_id) AS total_customers 
FROM olist_customers_dataset;

-- Total Revenue
SELECT SUM(payment_value) AS total_revenue 
FROM olist_order_payments_dataset;


-- CORE DATASET (JOINED TABLE)

SELECT 
    o.order_id,
    o.order_purchase_timestamp,
    p.payment_value,
    c.customer_state
FROM olist_orders_dataset o
JOIN olist_order_payments_dataset p 
    ON o.order_id = p.order_id
JOIN olist_customers_dataset c 
    ON o.customer_id = c.customer_id;


-- KPI ANALYSIS

-- Revenue Over Time
SELECT 
    DATE(order_purchase_timestamp) AS order_date,
    SUM(payment_value) AS total_sales
FROM olist_orders_dataset o
JOIN olist_order_payments_dataset p 
    ON o.order_id = p.order_id
GROUP BY order_date
ORDER BY order_date;

-- Average Order Value
SELECT 
    SUM(payment_value) / COUNT(DISTINCT o.order_id) AS AOV
FROM olist_orders_dataset o
JOIN olist_order_payments_dataset p 
    ON o.order_id = p.order_id;


-- ADVANCED ANALYSIS

-- Monthly Revenue Growth
SELECT 
    strftime('%Y-%m', order_purchase_timestamp) AS month,
    SUM(payment_value) AS revenue,
    LAG(SUM(payment_value)) OVER (
        ORDER BY strftime('%Y-%m', order_purchase_timestamp)
    ) AS prev_month
FROM olist_orders_dataset o
JOIN olist_order_payments_dataset p 
    ON o.order_id = p.order_id
GROUP BY month;


-- CUSTOMER ORDER FREQUENCY

SELECT 
    customer_id,
    COUNT(order_id) AS order_count
FROM olist_orders_dataset
GROUP BY customer_id;

SELECT 
    order_count,
    COUNT(*) AS customer_count
FROM (
    SELECT 
        customer_id,
        COUNT(order_id) AS order_count
    FROM olist_orders_dataset
    GROUP BY customer_id
) t
GROUP BY order_count
ORDER BY order_count;


-- CUSTOMER RETENTION

SELECT 
    CASE 
        WHEN order_count = 1 THEN 'One-time'
        ELSE 'Repeat'
    END AS customer_type,
    COUNT(*) AS customer_count
FROM (
    SELECT 
        customer_id,
        COUNT(order_id) AS order_count
    FROM olist_orders_dataset
    GROUP BY customer_id
) t
GROUP BY customer_type;


WITH first_purchase AS (
    SELECT 
        customer_id,
        MIN(strftime('%Y-%m', order_purchase_timestamp)) AS cohort_month
    FROM olist_orders_dataset
    GROUP BY customer_id
)
SELECT 
    f.cohort_month,
    strftime('%Y-%m', o.order_purchase_timestamp) AS order_month,
    COUNT(DISTINCT o.customer_id) AS customers
FROM olist_orders_dataset o
JOIN first_purchase f 
    ON o.customer_id = f.customer_id
GROUP BY f.cohort_month, order_month
ORDER BY f.cohort_month, order_month;