Design a Trading Database with SQL

import sqlite3
import pandas as pd
from datetime import datetime, timedelta
import random

conn = sqlite3.connect("trading.db")
cursor = conn.cursor()

cursor.executescript("""
CREATE TABLE IF NOT EXISTS instruments (
    id INTEGER PRIMARY KEY,
    symbol TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    asset_class TEXT NOT NULL,
    exchange TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS strategies (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    active BOOLEAN DEFAULT 1
);

CREATE TABLE IF NOT EXISTS trades (
    id INTEGER PRIMARY KEY,
    instrument_id INTEGER REFERENCES instruments(id),
    strategy_id INTEGER REFERENCES strategies(id),
    side TEXT CHECK(side IN ('BUY', 'SELL')),
    quantity REAL NOT NULL,
    price REAL NOT NULL,
    commission REAL DEFAULT 0,
    executed_at TIMESTAMP NOT NULL,
    notes TEXT
);

CREATE INDEX IF NOT EXISTS idx_trades_instrument ON trades(instrument_id);
CREATE INDEX IF NOT EXISTS idx_trades_strategy ON trades(strategy_id);
CREATE INDEX IF NOT EXISTS idx_trades_date ON trades(executed_at);
""")

print("Database schema created")

instruments = [
    ("AAPL", "Apple Inc", "Equity", "NASDAQ"),
    ("MSFT", "Microsoft Corp", "Equity", "NASDAQ"),
    ("GOOGL", "Alphabet Inc", "Equity", "NASDAQ"),
    ("SPY", "S&P 500 ETF", "ETF", "NYSE"),
    ("GLD", "Gold ETF", "ETF", "NYSE"),
]
cursor.executemany("INSERT OR IGNORE INTO instruments (symbol, name, asset_class, exchange) VALUES (?,?,?,?)", instruments)

strategies = [
    ("Momentum", "Trend-following strategy"),
    ("Mean Reversion", "Buy oversold, sell overbought"),
    ("Pairs Trading", "Market-neutral statistical arbitrage"),
]
cursor.executemany("INSERT OR IGNORE INTO strategies (name, description) VALUES (?,?)", strategies)

for _ in range(200):
    inst_id = random.randint(1, 5)
    strat_id = random.randint(1, 3)
    side = random.choice(["BUY", "SELL"])
    qty = random.randint(10, 500)
    price = round(random.uniform(50, 500), 2)
    commission = round(qty * 0.005, 2)
    date = datetime(2024, 1, 1) + timedelta(days=random.randint(0, 180))
    cursor.execute(
        "INSERT INTO trades (instrument_id, strategy_id, side, quantity, price, commission, executed_at) VALUES (?,?,?,?,?,?,?)",
        (inst_id, strat_id, side, qty, price, commission, date.isoformat())
    )

conn.commit()
print("Seeded 200 trades")

query = """
SELECT
    i.symbol,
    COUNT(t.id) as trade_count,
    SUM(t.quantity) as total_volume,
    ROUND(SUM(t.quantity * t.price), 2) as notional_value,
    ROUND(SUM(t.commission), 2) as total_commission
FROM trades t
JOIN instruments i ON t.instrument_id = i.id
GROUP BY i.symbol
ORDER BY notional_value DESC
"""

df = pd.read_sql_query(query, conn)
print(df.to_string(index=False))

query = """
SELECT
    s.name as strategy,
    COUNT(t.id) as trades,
    SUM(CASE WHEN t.side = 'BUY' THEN t.quantity * t.price ELSE 0 END) as bought,
    SUM(CASE WHEN t.side = 'SELL' THEN t.quantity * t.price ELSE 0 END) as sold,
    ROUND(SUM(t.commission), 2) as commissions
FROM trades t
JOIN strategies s ON t.strategy_id = s.id
GROUP BY s.name
"""

df = pd.read_sql_query(query, conn)
print(df.to_string(index=False))
conn.close()