Comprehensive SQL-powered business intelligence system
$0
+0%0
+0%0
+0%$0
+0%| Rank | Product Name | Revenue | Units Sold | Profit Margin |
|---|
-- Monthly revenue trend with year-over-year comparison
SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS month,
SUM(oi.quantity * oi.unit_price) AS monthly_revenue,
COUNT(DISTINCT o.order_id) AS total_orders,
AVG(oi.quantity * oi.unit_price) AS avg_order_value,
LAG(SUM(oi.quantity * oi.unit_price)) OVER (ORDER BY DATE_FORMAT(o.order_date, '%Y-%m')) AS prev_month_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY month;-- Top performing products with profit margins
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS total_revenue,
SUM(oi.quantity) AS units_sold,
ROUND((SUM(oi.quantity * oi.unit_price) - SUM(oi.quantity * p.cost_price)) /
SUM(oi.quantity * oi.unit_price) * 100, 2) AS profit_margin_pct,
RANK() OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS revenue_rank
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY p.product_id, p.product_name
HAVING total_revenue > 1000
ORDER BY total_revenue DESC
LIMIT 10;