Lesson 10: Mini-Project — Sales Analytics with SQL + pandas

import pandas as pd
from sqlalchemy import create_engine, text
 
# In-memory SQLite — no file system access needed
engine = create_engine("sqlite:///:memory:", echo=False)
 
# --- Seed DataFrames ---
 
customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4, 5],
    "name":        ["Alice Chen", "Bob Smith", "Carol Park", "Dave Lee", "Eve Wang"],
    "segment":     ["Premium", "Standard", "Premium", "Standard", "Premium"],
    "city":        ["New York", "Chicago", "New York", "Houston", "Seattle"],
})
 
products = pd.DataFrame({
    "product_id": [101, 102, 103, 104, 105],
    "name":       ["Widget A", "Gadget Pro", "Tool Kit", "Smart Device", "Basic Pack"],
    "category":   ["Hardware", "Electronics", "Hardware", "Electronics", "Accessories"],
    "unit_price": [29.99, 149.99, 49.99, 299.99, 9.99],
})
 
orders = pd.DataFrame({
    "order_id":    range(1, 21),
    "customer_id": [1,2,3,4,5,1,2,3,4,5,1,2,3,4,5,1,2,3,4,5],
    "product_id":  [101,102,103,104,105,102,103,104,105,101,103,104,105,101,102,104,105,101,102,103],
    "quantity":    [2,1,3,1,5,1,2,1,4,1,3,1,2,2,1,1,3,2,1,4],
    "order_date":  pd.date_range("2024-01-15", periods=20, freq="4D").strftime("%Y-%m-%d").tolist(),
})
 
# --- Load to DB ---
customers.to_sql("customers", engine, if_exists="replace", index=False)
products.to_sql("products",   engine, if_exists="replace", index=False)
orders.to_sql("orders",       engine, if_exists="replace", index=False)
 
# Verify
with engine.connect() as conn:
    for table in ["customers", "products", "orders"]:
        count = conn.execute(text(f"SELECT COUNT(*) FROM {table}")).scalar()
        print(f"  {table}: {count} rows")
 
print("\nSchema loaded successfully")
print(f"Date range: {orders['order_date'].min()} to {orders['order_date'].max()}")

# Preview each table
print("=== Customers ===")
print(pd.read_sql_table("customers", engine))
 
print("\n=== Products ===")
print(pd.read_sql_table("products", engine))
 
print("\n=== Orders (first 5) ===")
print(pd.read_sql("SELECT * FROM orders LIMIT 5", engine))
print(f"Total orders: {pd.read_sql('SELECT COUNT(*) AS n FROM orders', engine).iloc[0, 0]}")

revenue_by_segment = pd.read_sql("""
    SELECT
        c.segment,
        COUNT(DISTINCT o.order_id)             AS order_count,
        SUM(o.quantity * p.unit_price)         AS total_revenue,
        ROUND(AVG(o.quantity * p.unit_price), 2) AS avg_order_value
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products  p ON o.product_id  = p.product_id
    GROUP BY c.segment
    ORDER BY total_revenue DESC
""", engine)
 
print("=== Revenue by Segment ===")
print(revenue_by_segment.to_string(index=False))

product_revenue = pd.read_sql("""
    SELECT
        p.name,
        p.category,
        p.unit_price,
        SUM(o.quantity)                  AS units_sold,
        ROUND(SUM(o.quantity * p.unit_price), 2) AS total_revenue
    FROM products p
    LEFT JOIN orders o ON p.product_id = o.product_id
    GROUP BY p.product_id, p.name, p.category, p.unit_price
    ORDER BY total_revenue DESC
""", engine)
 
print("=== Product Revenue Ranking ===")
print(product_revenue.to_string(index=False))

monthly_revenue = pd.read_sql("""
    SELECT
        strftime('%Y-%m', o.order_date)           AS month,
        c.segment,
        ROUND(SUM(o.quantity * p.unit_price), 2)  AS revenue
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products  p ON o.product_id  = p.product_id
    GROUP BY month, c.segment
    ORDER BY month, c.segment
""", engine)
 
print("=== Monthly Revenue by Segment ===")
print(monthly_revenue.to_string(index=False))

# product_revenue DataFrame from Part 2
product_revenue["revenue_pct"] = (
    product_revenue["total_revenue"] / product_revenue["total_revenue"].sum() * 100
).round(1)
 
product_revenue["cumulative_revenue"] = product_revenue["total_revenue"].cumsum().round(2)
 
print("=== Product Revenue with Share and Cumulative ===")
print(product_revenue[["name", "category", "total_revenue", "revenue_pct", "cumulative_revenue"]]
      .to_string(index=False))

# monthly_revenue DataFrame from Part 2
pivot = monthly_revenue.pivot(
    index="month",
    columns="segment",
    values="revenue"
).fillna(0)
 
# Add total column
pivot["Total"] = pivot.sum(axis=1)
 
print("=== Monthly Revenue Pivot (segments as columns) ===")
print(pivot.to_string())

# Compute MoM growth percentage for each segment
pivot_growth = pivot.copy()
for col in [c for c in pivot.columns if c != "Total"]:
    growth_col = f"{col}_MoM%"
    pivot_growth[growth_col] = pivot[col].pct_change() * 100
 
# Show months with MoM data (first month has no prior month)
mom_cols = [c for c in pivot_growth.columns if "MoM%" in c]
print("=== Month-over-Month Growth % ===")
print(pivot_growth[mom_cols].round(1).to_string())

customer_summary = pd.read_sql("""
    SELECT
        c.name,
        c.segment,
        c.city,
        COUNT(o.order_id)                         AS order_count,
        ROUND(SUM(o.quantity * p.unit_price), 2)  AS lifetime_value
    FROM customers c
    LEFT JOIN orders   o ON c.customer_id = o.customer_id
    LEFT JOIN products p ON o.product_id  = p.product_id
    GROUP BY c.customer_id, c.name, c.segment, c.city
    ORDER BY lifetime_value DESC
""", engine)
 
# pandas: compute LTV percentile ranking
customer_summary["ltv_rank"] = customer_summary["lifetime_value"].rank(
    ascending=False, method="min"
).astype(int)
 
print("=== Customer Lifetime Value ===")
print(customer_summary.to_string(index=False))

# New batch of 5 orders (order_ids 21-25)
new_orders = pd.DataFrame({
    "order_id":    [21, 22, 23, 24, 25],
    "customer_id": [1,  3,  5,  2,  4],
    "product_id":  [104, 102, 103, 104, 101],
    "quantity":    [1,   2,   3,   1,   4],
    "order_date":  ["2024-04-01", "2024-04-05", "2024-04-08", "2024-04-10", "2024-04-12"],
})
 
# Load using INSERT OR REPLACE via raw SQL for upsert semantics
with engine.begin() as conn:
    for _, row in new_orders.iterrows():
        conn.execute(text("""
            INSERT OR REPLACE INTO orders (order_id, customer_id, product_id, quantity, order_date)
            VALUES (:order_id, :customer_id, :product_id, :quantity, :order_date)
        """), row.to_dict())
 
total_orders = pd.read_sql("SELECT COUNT(*) AS n FROM orders", engine).iloc[0, 0]
print(f"Total orders after upsert: {total_orders}")
 
# Re-run revenue by segment to verify update
revenue_updated = pd.read_sql("""
    SELECT
        c.segment,
        COUNT(DISTINCT o.order_id) AS order_count,
        ROUND(SUM(o.quantity * p.unit_price), 2) AS total_revenue
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products  p ON o.product_id  = p.product_id
    GROUP BY c.segment
    ORDER BY total_revenue DESC
""", engine)
print("\n=== Updated Revenue by Segment ===")
print(revenue_updated.to_string(index=False))

# Approach 1: SQL subquery
sql_approach = pd.read_sql("""
    SELECT
        c.name,
        COUNT(DISTINCT p.category) AS category_count,
        GROUP_CONCAT(DISTINCT p.category) AS categories
    FROM customers c
    JOIN orders   o ON c.customer_id = o.customer_id
    JOIN products p ON o.product_id  = p.product_id
    GROUP BY c.customer_id, c.name
    HAVING category_count >= 3
    ORDER BY category_count DESC
""", engine)
 
print("=== Customers with 3+ Categories (SQL) ===")
print(sql_approach.to_string(index=False))
 
# Approach 2: pandas groupby
orders_enriched = pd.read_sql("""
    SELECT o.customer_id, c.name, p.category
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products  p ON o.product_id  = p.product_id
""", engine)
 
pandas_approach = (
    orders_enriched
    .groupby(["customer_id", "name"])["category"]
    .nunique()
    .reset_index()
    .rename(columns={"category": "unique_categories"})
    .query("unique_categories >= 3")
    .sort_values("unique_categories", ascending=False)
)
print("\n=== Customers with 3+ Categories (pandas) ===")
print(pandas_approach.to_string(index=False))

# This cell documents the architectural reflection — run it to see the output
 
reflections = {
    "SQL was better for": [
        "Multi-table JOINs — databases execute joins efficiently with indexes",
        "GROUP BY aggregations — SUM/COUNT/AVG at the database level",
        "Filtering before Python — WHERE clause reduces data transferred",
        "Upsert logic — INSERT OR REPLACE is atomic at the DB level",
    ],
    "pandas was better for": [
        "pivot() — reshaping data by segment into columns",
        "pct_change() — computing MoM growth across rows",
        "rank() — assigning LTV rankings",
        "cumsum() — building cumulative revenue column",
        "Complex Python logic — any transformation requiring Python functions",
    ],
}
 
print("=== Reflection: SQL vs pandas ===\n")
for category, items in reflections.items():
    print(f"{category}:")
    for item in items:
        print(f"  - {item}")
    print()