DuckDB + SQLite: Build a Lightweight BI Dashboard at Zero Cost

pip install duckdb fastapi uvicorn pandas pyarrow

import sqlite3
import random
from datetime import datetime, timedelta

conn = sqlite3.connect("business.db")
cur = conn.cursor()

cur.execute("""
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    order_date TEXT,
    customer_id TEXT,
    product TEXT,
    quantity INTEGER,
    amount REAL,
    region TEXT
)
""")

cur.execute("""
CREATE TABLE IF NOT EXISTS customers (
    id TEXT PRIMARY KEY,
    name TEXT,
    signup_date TEXT,
    tier TEXT
)
""")

# Generate 500 orders
random.seed(42)
products = ["Pro", "Enterprise", "Basic", "API Calls", "Storage"]
regions = ["East", "South", "North", "West", "Central"]

for i in range(1, 501):
    date = datetime(2025, 1, 1) + timedelta(days=random.randint(0, 365))
    cur.execute(
        "INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?, ?)",
        (i, date.strftime("%Y-%m-%d"), f"C{random.randint(1,100):03d}",
         random.choice(products), random.randint(1, 20),
         round(random.uniform(50, 5000), 2), random.choice(regions))
    )

# Generate 100 customers
customers = [
    (f"C{i:03d}", f"Customer {i}", f"2024-{random.randint(1,12):02d}-01",
     random.choice(["Free", "Standard", "Premium"]))
    for i in range(1, 101)
]
cur.executemany("INSERT INTO customers VALUES (?, ?, ?, ?)", customers)

conn.commit()
conn.close()
print(f"✅ Created {len(customers)} customers, 500 orders")

import duckdb

con = duckdb.connect()

# Key: ATTACH the SQLite file
con.execute("ATTACH 'business.db' AS biz (TYPE SQLITE)")

# Query 1: Monthly Revenue Trend
monthly_revenue = con.execute("""
    SELECT
        DATE_TRUNC('month', order_date::DATE) AS month,
        SUM(amount) AS revenue,
        COUNT(*) AS orders,
        ROUND(AVG(amount), 2) AS avg_order_value
    FROM biz.orders
    GROUP BY month
    ORDER BY month
""").fetchdf()

print("📈 Monthly Revenue Trend:")
for _, row in monthly_revenue.iterrows():
    print(f"  {row.month}: ${row.revenue:,.0f} | Orders {int(row.orders)} | AOV ${row.avg_order_value:.0f}")

# Query 2: Customer Tier Cross-Analysis
customer_analysis = con.execute("""
    WITH customer_orders AS (
        SELECT
            c.id, c.tier,
            SUM(o.amount) AS total_spend,
            COUNT(DISTINCT o.product) AS products_bought,
            COUNT(*) AS order_count
        FROM biz.customers c
        LEFT JOIN biz.orders o ON c.id = o.customer_id
        GROUP BY c.id, c.tier
    )
    SELECT
        tier,
        COUNT(*) AS customer_count,
        ROUND(AVG(total_spend), 0) AS avg_spend,
        ROUND(SUM(total_spend), 0) AS total_revenue,
        ROUND(AVG(order_count), 1) AS avg_orders
    FROM customer_orders
    GROUP BY tier
    ORDER BY total_revenue DESC
""").fetchdf()

print("\n👥 Customer Tier Analysis:")
for _, row in customer_analysis.iterrows():
    print(f"  {row.tier}: {int(row.customer_count)} users | Avg Spend ${row.avg_spend:,.0f} | Total Revenue ${row.total_revenue:,.0f}")

# Query 3: Identify VIP Customers
vip_list = con.execute("""
    SELECT c.id, c.name, c.tier, SUM(o.amount) AS total_spend, COUNT(*) AS order_count
    FROM biz.customers c
    JOIN biz.orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name, c.tier
    HAVING SUM(o.amount) > 5000 OR COUNT(*) >= 10
    ORDER BY total_spend DESC
    LIMIT 20
""").fetchall()

print("\n⭐ VIP Customers TOP 20:")
for v in vip_list:
    print(f"  {v[1]} ({v[0]}): ${v[3]:,.0f} | {int(v[4])} orders")

import json

report = {
    "monthly_revenue": monthly_revenue.to_dict(orient="records"),
    "tier_analysis": customer_analysis.to_dict(orient="records"),
    "vip_customers": [list(v) for v in vip_list]
}

with open("analysis_report.json", "w", encoding="utf-8") as f:
    json.dump(report, f, ensure_ascii=False, indent=2)

print("✅ Report exported to analysis_report.json")

from fastapi import FastAPI
import duckdb

app = FastAPI()

@app.get("/api/revenue/monthly")
def get_monthly_revenue():
    con = duckdb.connect()
    con.execute("ATTACH 'business.db' AS biz (TYPE SQLITE)")
    result = con.execute("""
        SELECT DATE_TRUNC('month', order_date::DATE) AS month,
               SUM(amount) AS revenue, COUNT(*) AS orders
        FROM biz.orders GROUP BY month ORDER BY month
    """).fetchdf().to_dict(orient="records")
    con.close()
    return result

@app.get("/api/customers/vip")
def get_vip_customers():
    con = duckdb.connect()
    con.execute("ATTACH 'business.db' AS biz (TYPE SQLITE)")
    result = con.execute("""
        SELECT c.id, c.name, SUM(o.amount) AS total_spend, COUNT(*) AS order_count
        FROM biz.customers c JOIN biz.orders o ON c.id = o.customer_id
        GROUP BY c.id, c.name
        HAVING SUM(o.amount) > 5000
        ORDER BY total_spend DESC LIMIT 50
    """).fetchall()
    con.close()
    return [{"id": r[0], "name": r[1], "total_spend": r[2], "order_count": r[3]} for r in result]

uvicorn main:app --host 0.0.0.0 --port 8000

import duckdb

con = duckdb.connect()
con.execute("ATTACH 'business.db' AS biz (TYPE SQLITE)")

# Export to Parquet
con.execute("COPY (SELECT * FROM biz.orders) TO 'orders.parquet' (FORMAT PARQUET)")

# Then query directly from Parquet
parquet_con = duckdb.connect()
result = parquet_con.execute("""
    SELECT DATE_TRUNC('month', order_date::DATE) AS month,
           SUM(amount) AS revenue
    FROM read_parquet('orders.parquet')
    GROUP BY month ORDER BY month
""").fetchdf()