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()