02_revenue_analysis.sql

-- Total revenue (GMV) and total number of orders per month
CREATE TEMP VIEW monthly_revenue_order AS
SELECT TO_CHAR(o.order_purchase_timestamp, 'YYYY-MM') AS order_month,
	   SUM(i.price + i.freight_value) AS total_revenue,
	   COUNT(DISTINCT o.order_id) AS total_orders
FROM delivered_orders o
LEFT JOIN order_items i
	ON o.order_id = i.order_id
GROUP BY order_month;

SELECT *
FROM monthly_revenue_order;

-- Month-over-month (MoM) growth rate of revenue
WITH monthly_revenue_with_previous AS (
	SELECT order_month, total_revenue, LAG(total_revenue) OVER(ORDER BY order_month) AS previous_revenue
	FROM monthly_revenue_order
)
SELECT order_month, total_revenue,
	   ROUND((total_revenue - previous_revenue) * 100 / NULLIF(previous_revenue,0), 2) AS revenue_growth_rate
FROM monthly_revenue_with_previous;

-- Top 10 product categories generated the most revenue in all-time
SELECT category_eng,
     SUM(i.price + i.freight_value) AS total_sales,
	   COUNT(i.order_id) AS items_sold,
	   ROUND(SUM(i.price + i.freight_value) / COUNT(i.order_id), 2) AS avg_item_value
FROM delivered_order_items i
LEFT JOIN product_with_eng_category p
	ON i.product_id = p.product_id
GROUP BY category_eng
ORDER BY total_sales DESC
LIMIT 10;


-- Monthly Top Product Category
WITH monthly_sales_per_category AS (
	SELECT TO_CHAR(o.order_purchase_timestamp, 'YYYY-MM') AS order_month,
		   category_eng,
		   SUM(i.price + i.freight_value) AS total_sales
	FROM delivered_orders o
	LEFT JOIN order_items i
		ON o.order_id = i.order_id
	LEFT JOIN product_with_eng_category p
		ON i.product_id = p.product_id
	GROUP BY order_month, category_eng
),
category_monthly_rank AS (
	SELECT *, RANK() OVER(PARTITION BY order_month ORDER BY total_sales DESC) AS ranking
	FROM monthly_sales_per_category
)
SELECT cat.order_month, category_eng as top_category, ROUND(cat.total_sales / rvn.total_revenue * 100, 2) AS sales_pct
FROM category_monthly_rank cat
LEFT JOIN monthly_revenue_order rvn
	ON cat.order_month = rvn.order_month
WHERE ranking = 1;