olist_sql.sql

-- Q1: Distribution of order prices
SELECT 
    ROUND(AVG(price), 2) AS mean_price,
    ROUND(MIN(price), 2) AS min_price,
    ROUND(MAX(price), 2) AS max_price
FROM order_items;

-- Q2: Most popular product categories by volume
SELECT 
    t.product_category_name_english AS category,
    COUNT(oi.order_id) AS total_orders
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN translation t ON p.product_category_name = t.product_category_name
GROUP BY category
ORDER BY total_orders DESC
LIMIT 10;

-- Q4: Most common payment method used by customers


SELECT payment_type, COUNT(*) AS total
FROM payments
GROUP BY payment_type
ORDER BY total DESC;

--Bivariate
-- Q5 — Is there a correlation between the weight of a product and its shipping cost?

SELECT p.product_weight_g as "Product Weight",
	   oi.freight_value as "Freight Price"
FROM order_items oi
JOIN products p on oi.product_id = p.product_id where p.product_weight_g is not null;

-- Q 6.  Do customers who spend more money give higher or lower review scores?

SELECT 
    re.review_score,
    round(AVG(pa.payment_value), 2) AS avg_payment
FROM payments pa
JOIN reviews re ON pa.order_id = re.order_id
GROUP BY re.review_score
ORDER BY re.review_score ASC;

-- Q 7.  What is the total revenue generated per Brazilian state?

SELECT
	sum(pa.payment_value) as "Total spent",
	c.customer_state as "Customer State"
FROM payments pa
JOIN orders o on pa.order_id = o.order_id
JOIN customers c on o.customer_id = c.customer_id
GROUP by c.customer_state
ORDER by sum(pa.payment_value) DESC;
	

-- Q 8.  What is the average delivery time per state?

SELECT 	c.customer_state as State,
		round(avg(julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp)), 1) as avg_delivery_days

FROM orders o
JOIN customers c on o.customer_id = c.customer_id
WHERE o.order_delivered_customer_date is not NULL
GROUP by c.customer_state
order by avg_delivery_days DESC;


-- Q 9.  Which cities have the highest average spending per customer?

SELECT 
    c.customer_city AS City,
    round(AVG(pa.payment_value), 2) AS avg_spending
FROM payments pa
JOIN orders o ON pa.order_id = o.order_id 
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.customer_city
ORDER BY avg_spending DESC
LIMIT 10;


-- Q 10. What is the average difference between estimated and actual delivery date,
-- and which states have the worst performance?

SELECT 
    c.customer_state AS state,
    ROUND(AVG(JULIANDAY(o.order_delivered_customer_date) - JULIANDAY(o.order_estimated_delivery_date)), 1) AS avg_delay_days
FROM orders o
JOIN customers c ON o.customer_id = c.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
ORDER BY avg_delay_days DESC;