Từ câu hỏi kinh doanh
đến dashboard
trong vài giây.

SELECT SUM(p.payment_value) AS total_revenue
FROM orders o
JOIN order_payments p USING (order_id)
WHERE EXTRACT(YEAR FROM o.order_purchase_timestamp) = 2017;

SELECT DATE_TRUNC('month', order_purchase_timestamp) AS month,
       COUNT(DISTINCT order_id) AS total_orders
FROM orders
WHERE EXTRACT(YEAR FROM order_purchase_timestamp) = 2017
GROUP BY 1 ORDER BY 1;

SELECT c.customer_state AS state,
       SUM(p.payment_value) AS revenue
FROM orders o
JOIN customers c USING (customer_id)
JOIN order_payments p USING (order_id)
WHERE EXTRACT(YEAR FROM o.order_purchase_timestamp) = 2017
GROUP BY 1 ORDER BY revenue DESC LIMIT 5;

SELECT t.product_category_name_english AS category,
       COUNT(DISTINCT i.order_id) AS total_orders
FROM order_items i
JOIN orders o USING (order_id)
JOIN products pr USING (product_id)
JOIN category_translation t USING (product_category_name)
WHERE EXTRACT(YEAR FROM o.order_purchase_timestamp) = 2017
GROUP BY 1 ORDER BY 2 DESC LIMIT 10;

SELECT order_status,
       COUNT(*) AS total_orders,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_pct
FROM orders
WHERE EXTRACT(YEAR FROM order_purchase_timestamp) = 2017
GROUP BY order_status ORDER BY total_orders DESC;