• Connects to a SQLite database of orders
• Runs SQL queries via pandas to compute monthly revenue, top products, and category breakdown
• Cleans the data (handles nulls, fixes types)
• Produces 3 charts: monthly revenue trend, top-10 products bar chart, category distribution
• Saves a dashboard PNG and prints a KPI summary

mkdir sales-dashboard && cd sales-dashboard
python -m venv venv
source venv/bin/activate   # Windows: venv\Scripts\activate
pip install pandas matplotlib seaborn

import sqlite3
import random
from datetime import datetime, timedelta

conn = sqlite3.connect('sales.db')
c = conn.cursor()

c.execute("""
    CREATE TABLE IF NOT EXISTS orders (
        order_id INTEGER PRIMARY KEY,
        customer_id INTEGER,
        product TEXT,
        category TEXT,
        price REAL,
        quantity INTEGER,
        order_date TEXT,
        country TEXT,
        status TEXT
    )
""")

products = [
    ('Laptop Pro',     'Electronics', 899.99),
    ('Wireless Mouse', 'Electronics', 29.99),
    ('Python Course',  'Education',   49.99),
    ('Standing Desk',  'Furniture',   349.99),
    ('Coffee Mug',     'Kitchen',      12.99),
    ('Yoga Mat',       'Sports',       24.99),
    ('Headphones',     'Electronics', 149.99),
    ('Notebook',       'Office',        8.99),
    ('Running Shoes',  'Sports',       89.99),
    ('Desk Lamp',      'Furniture',    39.99),
]

statuses = ['completed', 'completed', 'completed', 'pending', 'refunded']
countries = ['US', 'UK', 'DE', 'CA', 'AU', 'FR']

start = datetime(2024, 1, 1)
rows = []
for i in range(1, 2001):
    product, category, price = random.choice(products)
    days_offset = random.randint(0, 364)
    date = (start + timedelta(days=days_offset)).strftime('%Y-%m-%d')
    rows.append((i, random.randint(1, 500), product, category, price,
                 random.randint(1, 5), date, random.choice(countries),
                 random.choice(statuses)))

c.executemany("INSERT OR IGNORE INTO orders VALUES (?,?,?,?,?,?,?,?,?)", rows)
conn.commit()
conn.close()
print("Database created: sales.db")

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.gridspec as gridspec

# Connect and load
conn = sqlite3.connect('sales.db')

df = pd.read_sql("""
    SELECT order_id, customer_id, product, category,
           price, quantity, order_date, country, status
    FROM orders
    WHERE status = 'completed'
""", conn)
conn.close()

# Fix types
df['order_date'] = pd.to_datetime(df['order_date'])
df['revenue'] = df['price'] * df['quantity']

# Basic validation
print(f"Loaded {len(df):,} completed orders")
print(f"Date range: {df['order_date'].min().date()} to {df['order_date'].max().date()}")
print(f"Total revenue: ${df['revenue'].sum():,.2f}")
print(f"Null check:\n{df.isnull().sum()}")

# Monthly revenue
df['month'] = df['order_date'].dt.to_period('M')
monthly = df.groupby('month').agg(
    revenue=('revenue', 'sum'),
    orders=('order_id', 'count'),
    customers=('customer_id', 'nunique')
).reset_index()
monthly['month_str'] = monthly['month'].astype(str)
monthly['mom_growth'] = monthly['revenue'].pct_change() * 100

# Top 10 products by revenue
top_products = (
    df.groupby('product')['revenue']
    .sum()
    .sort_values(ascending=True)
    .tail(10)
)

# Revenue by category
by_category = df.groupby('category')['revenue'].sum().sort_values(ascending=False)

fig = plt.figure(figsize=(16, 12))
fig.suptitle('E-commerce Sales Dashboard — 2024', fontsize=16, fontweight='bold', y=0.98)

gs = gridspec.GridSpec(2, 2, figure=fig, hspace=0.4, wspace=0.3)

# ── Chart 1: Monthly Revenue Trend ──────────────────────────────
ax1 = fig.add_subplot(gs[0, :])  # Full width, top row
ax1.plot(monthly['month_str'], monthly['revenue'],
         marker='o', linewidth=2, color='#2563EB', markersize=6)
ax1.fill_between(monthly['month_str'], monthly['revenue'], alpha=0.1, color='#2563EB')

# Annotate peak month
peak_idx = monthly['revenue'].idxmax()
ax1.annotate(
    f"Peak: ${monthly.loc[peak_idx, 'revenue']:,.0f}",
    xy=(monthly.loc[peak_idx, 'month_str'], monthly.loc[peak_idx, 'revenue']),
    xytext=(0, 15), textcoords='offset points', ha='center', fontsize=9,
    arrowprops=dict(arrowstyle='->', color='gray')
)

ax1.set_title('Monthly Revenue', fontsize=12, fontweight='bold')
ax1.set_ylabel('Revenue ($)')
ax1.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, _: f'${x:,.0f}'))
ax1.tick_params(axis='x', rotation=45)
ax1.grid(axis='y', alpha=0.3)

# ── Chart 2: Top 10 Products ─────────────────────────────────────
ax2 = fig.add_subplot(gs[1, 0])
colors = ['#2563EB' if v == top_products.max() else '#93C5FD' for v in top_products]
ax2.barh(top_products.index, top_products.values, color=colors)
ax2.set_title('Top 10 Products by Revenue', fontsize=11, fontweight='bold')
ax2.set_xlabel('Revenue ($)')
ax2.xaxis.set_major_formatter(plt.FuncFormatter(lambda x, _: f'${x:,.0f}'))

# ── Chart 3: Revenue by Category ─────────────────────────────────
ax3 = fig.add_subplot(gs[1, 1])
ax3.bar(by_category.index, by_category.values, color='#10B981', alpha=0.8)
ax3.set_title('Revenue by Category', fontsize=11, fontweight='bold')
ax3.set_ylabel('Revenue ($)')
ax3.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, _: f'${x:,.0f}'))
ax3.tick_params(axis='x', rotation=30)

plt.savefig('dashboard.png', dpi=150, bbox_inches='tight')
plt.show()
print("Dashboard saved: dashboard.png")

print("\n" + "="*50)
print("KPI SUMMARY")
print("="*50)
print(f"Total Revenue:     ${df['revenue'].sum():>12,.2f}")
print(f"Total Orders:      {len(df):>12,}")
print(f"Unique Customers:  {df['customer_id'].nunique():>12,}")
print(f"Avg Order Value:   ${df['revenue'].mean():>12,.2f}")
print(f"Best Month:        {monthly.loc[peak_idx, 'month_str']:>12}")
print(f"Best Category:     {by_category.index[0]:>12}")
print("="*50)

• [ ] SQLite database created with 2,000 sample orders
• [ ] Data loaded via pd.read_sql() and cleaned (dtypes fixed, revenue column computed)
• [ ] Monthly revenue trend chart with peak annotated
• [ ] Top-10 products horizontal bar chart
• [ ] Revenue by category bar chart
• [ ] Dashboard saved as PNG with tight_layout
• [ ] KPI summary printed to console