Buisness_performance.sql

-- 1.What is the total revenue generated by the platform?
-- 2.How does monthly revenue trend over time?
-- 3.What is the month-over-month revenue growth percentage?
-- 4.How many orders are placed each month*? 
-- What is the average order value (AOV) overall?
-- 6.What percentage of orders are cancelled or unavailable?

-- 1
select round(sum(oi.price+oi.freight_value),2) as total_revenue 
from order_items oi
join orders o
on oi.order_id=o.order_id
where o.order_status='delivered';

-- 2,3
WITH monthly_revenue AS (
    SELECT
        DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') AS order_month,
        SUM(oi.price + oi.freight_value) AS revenue
    FROM orders o
    JOIN order_items oi
        ON o.order_id = oi.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY order_month
)
SELECT
    order_month,
    ROUND(revenue, 2) AS revenue,
    ROUND(
        ((revenue - LAG(revenue) OVER (ORDER BY order_month))
        / LAG(revenue) OVER (ORDER BY order_month)) * 100,
        2
    ) AS mom_growth_percentage
FROM monthly_revenue
ORDER BY order_month;

-- 5
select count(orders.order_id),
date_format(orders.order_purchase_timestamp,'%Y-%m') as order_month
from orders
group by order_month
order by order_month;

with aov as
(select count(distinct o.order_id) as no_of_orders,
round(sum(oi.price+oi.freight_value),2) as total_revenue
from orders o 
join order_items oi on oi.order_id=o.order_id
where o.order_status='delivered'
)
select round(total_revenue/no_of_orders) as AOV
from aov;

-- 6
WITH order_counts AS (
    SELECT
        COUNT(*) AS total_orders,
        SUM(
            CASE 
                WHEN order_status IN ('cancelled', 'unavailable') 
                THEN 1 ELSE 0 
            END
        ) AS failed_orders
    FROM orders
)

SELECT
    ROUND((failed_orders * 100.0) / total_orders, 2) 
    AS cancelled_or_unavailable_percentage
FROM order_counts;