olist_sql_file.sql

CREATE DATABASE olist;
USE olist;

CREATE TABLE orders_master (
    order_id VARCHAR(50),
    order_item_id INT,
    product_id VARCHAR(50),
    seller_id VARCHAR(50),
    price DECIMAL(10,2),
    freight_value DECIMAL(10,2),
    product_category_name_english VARCHAR(100),
    seller_state VARCHAR(10),
    customer_id VARCHAR(50),
    order_status VARCHAR(20),
    order_purchase_timestamp DATETIME,
    customer_state VARCHAR(10),
    payment_value DECIMAL(10,2),
    payment_type VARCHAR(20),
    review_score FLOAT
);

SELECT * FROM orders_master;

SELECT COUNT(*) FROM orders_master;

-- Monthly Revenue

SELECT
    DATE_FORMAT(order_purchase_timestamp, '%Y-%m') AS month,
    ROUND(SUM(payment_value), 2) AS revenue
FROM orders_master
GROUP BY DATE_FORMAT(order_purchase_timestamp, '%Y-%m')
ORDER BY DATE_FORMAT(order_purchase_timestamp, '%Y-%m');

-- Top 10 Categories by Revenue

SELECT 
    product_category_name_english AS category,
    ROUND(SUM(payment_value), 2) AS revenue
FROM orders_master
WHERE product_category_name_english IS NOT NULL
GROUP BY category
ORDER BY revenue DESC
LIMIT 10;

-- Top State with high orders

SELECT
	customer_state,
    COUNT(order_id) AS order_count
FROM orders_master
GROUP BY customer_state
ORDER BY order_count DESC
LIMIT 10;

-- Review Score

SELECT 
    review_score,
    COUNT(*) AS count
FROM orders_master
WHERE review_score IS NOT NULL
GROUP BY review_score
ORDER BY review_score;

-- Payment Type

SELECT 
    payment_type,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS percentage
FROM orders_master
WHERE payment_type IS NOT NULL
GROUP BY payment_type
ORDER BY count DESC;

-- RFM Segment

SELECT 
    segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(monetary), 2) AS avg_monetary,
    ROUND(AVG(recency), 2) AS avg_recency
FROM rfm_segments
GROUP BY segment
ORDER BY customer_count DESC;

-- Average order value 

SELECT 
    customer_state,
    COUNT(*) AS orders,
    ROUND(AVG(payment_value), 2) AS avg_order_value
FROM orders_master
WHERE customer_state IN ('SP', 'RJ')
GROUP BY customer_state;

-- Segment Final 

SELECT 
    segment,
    COUNT(*) AS customers,
    ROUND(AVG(monetary), 2) AS avg_monetary,
    ROUND(AVG(monetary / frequency), 2) AS avg_order_value,
    ROUND(AVG(frequency), 2) AS avg_frequency
FROM rfm_segments
GROUP BY segment
ORDER BY avg_monetary DESC;

-- Reviews

SELECT * FROM delivery_data;
SELECT 
    CASE WHEN d.delivery_delay > 0 THEN 'Late' ELSE 'On-Time' END AS delivery_status,
    COUNT(*) AS orders,
    ROUND(AVG(o.review_score), 2) AS avg_review_score
FROM orders_master o
JOIN delivery_data d ON o.order_id = d.order_id
WHERE o.review_score IS NOT NULL
AND d.delivery_delay IS NOT NULL
GROUP BY delivery_status;


-- Avg delay vs Avg review

SELECT 
    o.seller_id,
    COUNT(DISTINCT o.order_id) AS total_orders,
    ROUND(AVG(o.review_score), 2) AS avg_review,
    ROUND(SUM(o.payment_value), 2) AS total_revenue,
    ROUND(AVG(d.delivery_delay), 2) AS avg_delay
FROM orders_master o
JOIN delivery_data d 
ON o.order_id = d.order_id
WHERE o.review_score IS NOT NULL
GROUP BY o.seller_id
HAVING total_orders >= 10
ORDER BY total_revenue DESC
LIMIT 10;