E-commerce Analytics Dashboard

Simulated PostgreSQL analytics for delivered orders, order value, products, customers, and categories.

Total Revenue

$0
Sum of all delivered order line items
SQL query 
SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'delivered'
  AND o.order_date >= DATE :start_date
  AND o.order_date < DATE :end_date + INTERVAL '1 day';

Average Order Value

$0
Total revenue / delivered orders
SQL query 
SELECT COALESCE(
         SUM(oi.quantity * oi.unit_price)
         / NULLIF(COUNT(DISTINCT o.id), 0),
         0
       ) AS average_order_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'delivered'
  AND o.order_date >= DATE :start_date
  AND o.order_date < DATE :end_date + INTERVAL '1 day';

Delivered Orders

0
Orders with status = delivered
SQL query 
SELECT COUNT(*) AS delivered_orders
FROM orders o
WHERE o.status = 'delivered'
  AND o.order_date >= DATE :start_date
  AND o.order_date < DATE :end_date + INTERVAL '1 day';

New Customers

0
Customers created in selected range
SQL query 
SELECT COUNT(*) AS new_customers
FROM customers c
WHERE c.created_at >= DATE :start_date
  AND c.created_at < DATE :end_date + INTERVAL '1 day';

Top 5 Best-Selling Products

By quantity sold from delivered orders
Data table
# Product Quantity sold
SQL query 
SELECT p.name AS product_name,
       COALESCE(SUM(oi.quantity), 0) AS quantity_sold
FROM products p
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'delivered'
  AND o.order_date >= DATE :start_date
  AND o.order_date < DATE :end_date + INTERVAL '1 day'
GROUP BY p.id, p.name
ORDER BY quantity_sold DESC
LIMIT 5;

Category Performance

Revenue by product category for delivered orders
Data table
Category Revenue Share
SQL query 
SELECT cat.name AS category,
       COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
FROM categories cat
JOIN products p ON p.category_id = cat.id
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'delivered'
  AND o.order_date >= DATE :start_date
  AND o.order_date < DATE :end_date + INTERVAL '1 day'
GROUP BY cat.id, cat.name
ORDER BY revenue DESC;

Customer Growth Over Time

Monthly new customer counts
Data table
Month New customers
SQL query 
SELECT DATE_TRUNC('month', c.created_at)::date AS month_start,
       COUNT(*) AS new_customers
FROM customers c
WHERE c.created_at >= DATE :start_date
  AND c.created_at < DATE :end_date + INTERVAL '1 day'
GROUP BY 1
ORDER BY 1;

-- SQLite equivalent for month bucket:
-- strftime('%Y-%m-01', c.created_at) AS month_start