SQL Views: E-commerce Analytics Dashboard

In this scenario, we will create an e-commerce analytics dashboard for an online store. The store has several tables: customers, orders, order_items, products, and categories. The goal is to create SQL views that provide insights into sales performance, customer behavior, and inventory management.

Table Structures

1. customers
   • customer_id (INT, Primary Key)
   • name (VARCHAR)
   • email (VARCHAR)
   • created_at (TIMESTAMP)
2. orders
   • order_id (INT, Primary Key)
   • customer_id (INT, Foreign Key)
   • order_date (TIMESTAMP)
   • status (VARCHAR)
3. order_items
   • order_item_id (INT, Primary Key)
   • order_id (INT, Foreign Key)
   • product_id (INT, Foreign Key)
   • quantity (INT)
   • price (DECIMAL)
4. products
   • product_id (INT, Primary Key)
   • name (VARCHAR)
   • category_id (INT, Foreign Key)
   • price (DECIMAL)
5. categories
   • category_id (INT, Primary Key)
   • category_name (VARCHAR)

View 1: Monthly Sales Performance

This view aggregates sales data by month to provide insights into the store’s monthly revenue.

CREATE VIEW monthly_sales_performance AS
SELECT
    DATE_TRUNC('month', o.order_date) AS month,
    SUM(oi.price * oi.quantity) AS total_revenue,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COUNT(DISTINCT o.customer_id) AS total_customers
FROM
    orders o
JOIN
    order_items oi ON o.order_id = oi.order_id
GROUP BY
    DATE_TRUNC('month', o.order_date)
ORDER BY
    month;

View 2: Top Performing Products

This view lists the top 10 products based on total sales revenue.

CREATE VIEW top_performing_products AS
SELECT
    p.product_id,
    p.name AS product_name,
    c.category_name,
    SUM(oi.price * oi.quantity) AS total_revenue,
    SUM(oi.quantity) AS total_quantity_sold
FROM
    products p
JOIN
    categories c ON p.category_id = c.category_id
JOIN
    order_items oi ON p.product_id = oi.product_id
GROUP BY
    p.product_id, p.name, c.category_name
ORDER BY
    total_revenue DESC
LIMIT 10;

View 3: Customer Purchase Behavior

This view provides insights into customer purchase behavior, including the number of orders and total spend.

CREATE VIEW customer_purchase_behavior AS
SELECT
    c.customer_id,
    c.name AS customer_name,
    c.email,
    COUNT(o.order_id) AS total_orders,
    SUM(oi.price * oi.quantity) AS total_spend,
    MIN(o.order_date) AS first_order_date,
    MAX(o.order_date) AS last_order_date
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
JOIN
    order_items oi ON o.order_id = oi.order_id
GROUP BY
    c.customer_id, c.name, c.email;

View 4: Inventory Status

This view provides information on the current stock status of each product category.

CREATE VIEW inventory_status AS
SELECT
    c.category_id,
    c.category_name,
    COUNT(p.product_id) AS total_products,
    SUM(p.price * (SELECT SUM(oi.quantity)
                   FROM order_items oi
                   WHERE oi.product_id = p.product_id)) AS total_inventory_value
FROM
    categories c
JOIN
    products p ON c.category_id = p.category_id
GROUP BY
    c.category_id, c.category_name;
CREATE VIEW inventory_status AS
SELECT
    c.category_id,
    c.category_name,
    COUNT(p.product_id) AS total_products,
    SUM(p.price * (SELECT SUM(oi.quantity)
                   FROM order_items oi
                   WHERE oi.product_id = p.product_id)) AS total_inventory_value
FROM
    categories c
JOIN
    products p ON c.category_id = p.category_id
GROUP BY
    c.category_id, c.category_name;

Usage

These views can now be used to generate reports or integrated into a business intelligence tool to provide real-time insights into the e-commerce platform’s performance. For example:

• Monthly Sales Performance: Track and compare monthly revenue trends.
• Top Performing Products: Identify best-selling products to optimize inventory and marketing strategies.
• Customer Purchase Behavior: Understand customer segments and tailor marketing efforts.
• Inventory Status: Manage stock levels and forecast inventory needs.

Advanced Techniques

1. Materialized Views: For performance reasons, consider using materialized views if the underlying data does not change frequently.
2. Indexes: Create indexes on columns used in JOINs and WHERE clauses to speed up query execution.
3. Partitioning: If the dataset is large, partitioning the tables by date (e.g., orders by month) can improve performance.