Real-Time Sales Dashboard

-- Create a dedicated database for retail sales analytics
CREATE DATABASE retail_analytics;

-- Connect to it (in psql: \c retail_analytics)

CREATE TABLE stores (
    store_id   SERIAL PRIMARY KEY,
    store_name VARCHAR(100) NOT NULL,
    region     VARCHAR(50)  NOT NULL,
    city       VARCHAR(80)  NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    category     VARCHAR(80)  NOT NULL,
    unit_price   DECIMAL(10, 2) NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sales (
    sale_id    SERIAL PRIMARY KEY,
    store_id   INTEGER NOT NULL REFERENCES stores(store_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    amount     DECIMAL(10, 2) NOT NULL,
    sale_date  DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_sales_sale_date ON sales(sale_date);
CREATE INDEX idx_sales_store_id  ON sales(store_id);
CREATE INDEX idx_sales_product_id ON sales(product_id);

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name IN ('stores', 'products', 'sales');

pip install psycopg2-binary

import psycopg2
import random
from datetime import datetime, timedelta

# --- CONFIG: Change these to match your PostgreSQL setup ---
CONN_PARAMS = {
    'host': 'localhost',
    'port': 5432,
    'user': 'postgres',
    'password': 'your_password',
    'dbname': 'retail_analytics'
}

def main():
    conn = psycopg2.connect(**CONN_PARAMS)
    cur = conn.cursor()

    # 1. Insert stores (5 stores across 3 regions)
    stores_data = [
        (1, 'Downtown Store', 'North', 'New York'),
        (2, 'Mall Outlet', 'North', 'Boston'),
        (3, 'Southside Plaza', 'South', 'Miami'),
        (4, 'West Coast Hub', 'West', 'Los Angeles'),
        (5, 'Central Square', 'Central', 'Chicago'),
    ]
    for row in stores_data:
        cur.execute(
            "INSERT INTO stores (store_id, store_name, region, city) VALUES (%s, %s, %s, %s) ON CONFLICT (store_id) DO NOTHING",
            row
        )

    # 2. Insert products (12 products across 4 categories)
    products_data = [
        (1, 'Wireless Mouse', 'Electronics', 29.99),
        (2, 'USB-C Hub', 'Electronics', 49.99),
        (3, 'Desk Lamp', 'Home', 34.99),
        (4, 'Coffee Maker', 'Home', 79.99),
        (5, 'Cotton T-Shirt', 'Clothing', 19.99),
        (6, 'Running Shoes', 'Clothing', 89.99),
        (7, 'Bluetooth Speaker', 'Electronics', 59.99),
        (8, 'Yoga Mat', 'Home', 24.99),
        (9, 'Backpack', 'Clothing', 44.99),
        (10, 'Keyboard', 'Electronics', 89.99),
        (11, 'Throw Pillow', 'Home', 29.99),
        (12, 'Sunglasses', 'Clothing', 39.99),
    ]
    for row in products_data:
        cur.execute(
            "INSERT INTO products (product_id, product_name, category, unit_price) VALUES (%s, %s, %s, %s) ON CONFLICT (product_id) DO NOTHING",
            row
        )

    # 3. Generate ~500 sales over the last 90 days
    start_date = datetime.now().date() - timedelta(days=90)
    sale_id = 1
    for _ in range(500):
        store_id = random.randint(1, 5)
        product_id = random.randint(1, 12)
        quantity = random.randint(1, 5)
        cur.execute("SELECT unit_price FROM products WHERE product_id = %s", (product_id,))
        unit_price = cur.fetchone()[0]
        amount = round(quantity * unit_price, 2)
        days_offset = random.randint(0, 90)
        sale_date = start_date + timedelta(days=days_offset)
        cur.execute(
            "INSERT INTO sales (sale_id, store_id, product_id, quantity, amount, sale_date) VALUES (%s, %s, %s, %s, %s, %s)",
            (sale_id, store_id, product_id, quantity, amount, sale_date)
        )
        sale_id += 1

    conn.commit()
    cur.close()
    conn.close()
    print("Done! Inserted stores, products, and ~500 sales.")

if __name__ == '__main__':
    main()

python seed_retail_data.py

SELECT 'stores' AS tbl, COUNT(*) AS cnt FROM stores
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'sales', COUNT(*) FROM sales;

docker run -d -p 3000:3000 --name metabase metabase/metabase

SELECT COUNT(*) FROM sales;

SELECT
    sale_date AS date,
    SUM(amount) AS total_revenue,
    COUNT(*) AS order_count
FROM sales
WHERE sale_date BETWEEN {{ start_date }} AND {{ end_date }}
GROUP BY sale_date
ORDER BY sale_date

SELECT
    s.store_name,
    s.region,
    SUM(f.amount) AS total_revenue,
    COUNT(f.sale_id) AS order_count
FROM sales f
JOIN stores s ON f.store_id = s.store_id
WHERE f.sale_date BETWEEN {{ start_date }} AND {{ end_date }}
GROUP BY s.store_id, s.store_name, s.region
ORDER BY total_revenue DESC

SELECT
    p.product_name,
    p.category,
    SUM(f.amount) AS total_revenue,
    SUM(f.quantity) AS units_sold,
    COUNT(f.sale_id) AS times_ordered
FROM sales f
JOIN products p ON f.product_id = p.product_id
WHERE f.sale_date BETWEEN {{ start_date }} AND {{ end_date }}
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC
LIMIT 10