E-commerce Store Analytics Dashboard

SQL Query Documentation

-- Assumed schema:
-- orders(order_id, customer_id, order_date, status)
-- order_items(order_item_id, order_id, product_id, quantity, unit_price)
-- products(product_id, product_name, category_id, price)
-- categories(category_id, category_name)
-- customers(customer_id, customer_name, created_at)

-- Date parameters used below: :start_date, :end_date.

-- 1. Total Revenue
SELECT
  SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'delivered'
  AND o.order_date BETWEEN :start_date AND :end_date;

-- 2. Average Order Value
SELECT
  SUM(oi.quantity * oi.unit_price) AS total_revenue,
  COUNT(DISTINCT o.order_id) AS delivered_orders,
  SUM(oi.quantity * oi.unit_price) / NULLIF(COUNT(DISTINCT o.order_id), 0) AS average_order_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'delivered'
  AND o.order_date BETWEEN :start_date AND :end_date;

-- 3. Top 5 Best-Selling Products by Quantity Sold
SELECT
  p.product_id,
  p.product_name,
  c.category_name,
  SUM(oi.quantity) AS quantity_sold,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
WHERE o.status = 'delivered'
  AND o.order_date BETWEEN :start_date AND :end_date
GROUP BY p.product_id, p.product_name, c.category_name
ORDER BY quantity_sold DESC
LIMIT 5;

-- 4. Customer Growth Over Time
SELECT
  DATE_TRUNC('month', customer.created_at) AS month,
  COUNT(*) AS new_customers
FROM customers customer
WHERE customer.created_at BETWEEN :start_date AND :end_date
GROUP BY DATE_TRUNC('month', customer.created_at)
ORDER BY month;

-- 5. Category Performance
SELECT
  c.category_name,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
WHERE o.status = 'delivered'
  AND o.order_date BETWEEN :start_date AND :end_date
GROUP BY c.category_name
ORDER BY revenue DESC;