CREATE TABLE trades (
    trade_id        SERIAL PRIMARY KEY,
    trade_date      DATE NOT NULL,
    trade_time      TIMESTAMP NOT NULL,
    trader          VARCHAR(50) NOT NULL,
    desk            VARCHAR(30) NOT NULL,
    ticker          VARCHAR(10) NOT NULL,
    side            VARCHAR(4) NOT NULL,    -- 'BUY' or 'SELL'
    quantity        INTEGER NOT NULL,
    price           DECIMAL(12, 4) NOT NULL,
    notional        DECIMAL(15, 2) GENERATED ALWAYS AS (quantity * price) STORED,
    currency        VARCHAR(3) DEFAULT 'USD',
    status          VARCHAR(10) DEFAULT 'FILLED'
);

CREATE TABLE positions (
    position_id     SERIAL PRIMARY KEY,
    desk            VARCHAR(30) NOT NULL,
    ticker          VARCHAR(10) NOT NULL,
    quantity        INTEGER NOT NULL,          -- Positive = long, negative = short
    avg_cost        DECIMAL(12, 4) NOT NULL,
    current_price   DECIMAL(12, 4),
    last_updated    TIMESTAMP DEFAULT NOW()
);

CREATE TABLE market_data (
    ticker          VARCHAR(10) NOT NULL,
    trade_date      DATE NOT NULL,
    open_price      DECIMAL(12, 4),
    high_price      DECIMAL(12, 4),
    low_price       DECIMAL(12, 4),
    close_price     DECIMAL(12, 4),
    volume          BIGINT,
    PRIMARY KEY (ticker, trade_date)
);

INSERT INTO trades (trade_date, trade_time, trader, desk, ticker, side, quantity, price)
VALUES
    ('2026-03-28', '2026-03-28 09:31:00', 'Alice', 'Equities', 'AAPL', 'BUY',  1000, 148.25),
    ('2026-03-28', '2026-03-28 09:45:00', 'Alice', 'Equities', 'MSFT', 'BUY',   500, 375.80),
    ('2026-03-28', '2026-03-28 10:12:00', 'Bob',   'Equities', 'GOOGL', 'SELL',  300, 152.40),
    ('2026-03-28', '2026-03-28 11:03:00', 'Alice', 'Equities', 'AAPL', 'BUY',   500, 149.10),
    ('2026-03-28', '2026-03-28 14:22:00', 'Charlie', 'Fixed Income', 'TLT', 'BUY', 2000, 95.50),
    ('2026-03-28', '2026-03-28 15:15:00', 'Bob',   'Equities', 'NVDA', 'BUY',   200, 825.30),
    ('2026-03-31', '2026-03-31 09:30:00', 'Alice', 'Equities', 'AAPL', 'SELL',  800, 151.20),
    ('2026-03-31', '2026-03-31 10:05:00', 'David', 'Derivatives', 'SPY', 'BUY', 1500, 520.45),
    ('2026-03-31', '2026-03-31 11:30:00', 'Bob',   'Equities', 'GOOGL', 'BUY',  400, 155.10),
    ('2026-03-31', '2026-03-31 14:00:00', 'Charlie', 'Fixed Income', 'TLT', 'SELL', 1000, 96.20);

-- All trades for a specific date
SELECT trade_id, trade_time, trader, ticker, side, quantity, price, notional
FROM trades
WHERE trade_date = '2026-03-28'
ORDER BY trade_time;

-- Alice's buys over $100K notional
SELECT ticker, side, quantity, price, notional
FROM trades
WHERE trader = 'Alice'
  AND side = 'BUY'
  AND notional > 100000
ORDER BY notional DESC;

-- All tech stock trades
SELECT trader, ticker, side, quantity, price
FROM trades
WHERE ticker IN ('AAPL', 'MSFT', 'GOOGL', 'NVDA', 'META', 'AMZN')
ORDER BY ticker, trade_time;

-- All traders whose name starts with 'A'
SELECT DISTINCT trader
FROM trades
WHERE trader LIKE 'A%';

SELECT
    trade_date,
    desk,
    COUNT(*) AS num_trades,
    SUM(notional) AS total_notional,
    ROUND(AVG(notional), 2) AS avg_trade_size
FROM trades
GROUP BY trade_date, desk
ORDER BY trade_date, total_notional DESC;

SELECT
    trader,
    COUNT(*) AS total_trades,
    SUM(quantity) AS total_shares,
    SUM(notional) AS total_notional,
    ROUND(AVG(price), 2) AS avg_price
FROM trades
GROUP BY trader
ORDER BY total_notional DESC;

-- Desks with more than $500K in daily notional
SELECT
    trade_date,
    desk,
    SUM(notional) AS total_notional
FROM trades
GROUP BY trade_date, desk
HAVING SUM(notional) > 500000
ORDER BY total_notional DESC;

-- Trades with current market prices
SELECT
    t.trade_date,
    t.ticker,
    t.side,
    t.price AS trade_price,
    m.close_price AS market_close,
    ROUND((m.close_price - t.price) * t.quantity, 2) AS unrealized_pnl
FROM trades t
INNER JOIN market_data m
    ON t.ticker = m.ticker
    AND t.trade_date = m.trade_date
WHERE t.side = 'BUY'
ORDER BY unrealized_pnl DESC;

-- All trades, with market data if available
SELECT
    t.trade_id,
    t.ticker,
    t.price AS trade_price,
    m.close_price,
    CASE
        WHEN m.close_price IS NULL THEN 'NO MARKET DATA'
        ELSE 'OK'
    END AS data_status
FROM trades t
LEFT JOIN market_data m
    ON t.ticker = m.ticker
    AND t.trade_date = m.trade_date;

-- Find pairs of trades in the same stock on the same day
-- (potential wash trades or paired activity)
SELECT
    t1.trade_id AS trade_1,
    t2.trade_id AS trade_2,
    t1.ticker,
    t1.trader AS trader_1,
    t2.trader AS trader_2,
    t1.side AS side_1,
    t2.side AS side_2,
    t1.quantity AS qty_1,
    t2.quantity AS qty_2
FROM trades t1
INNER JOIN trades t2
    ON t1.ticker = t2.ticker
    AND t1.trade_date = t2.trade_date
    AND t1.trade_id < t2.trade_id  -- Avoid duplicates and self-matches
    AND t1.side != t2.side          -- Opposite sides
ORDER BY t1.ticker, t1.trade_time;

SELECT
    trade_date,
    trade_time,
    trader,
    ticker,
    notional,
    SUM(notional) OVER (
        PARTITION BY trade_date
        ORDER BY trade_time
    ) AS running_total
FROM trades
ORDER BY trade_date, trade_time;

SELECT
    trader,
    SUM(notional) AS total_notional,
    RANK() OVER (ORDER BY SUM(notional) DESC) AS rank,
    ROUND(
        SUM(notional) * 100.0 / SUM(SUM(notional)) OVER (),
        1
    ) AS pct_of_total
FROM trades
GROUP BY trader
ORDER BY rank;

-- 5-day moving average of closing prices
SELECT
    ticker,
    trade_date,
    close_price,
    ROUND(
        AVG(close_price) OVER (
            PARTITION BY ticker
            ORDER BY trade_date
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ), 4
    ) AS ma_5day,
    ROUND(
        AVG(close_price) OVER (
            PARTITION BY ticker
            ORDER BY trade_date
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ), 4
    ) AS ma_20day
FROM market_data
WHERE ticker = 'AAPL'
ORDER BY trade_date DESC
LIMIT 10;

-- Daily price change and percentage change
SELECT
    ticker,
    trade_date,
    close_price,
    LAG(close_price) OVER (
        PARTITION BY ticker ORDER BY trade_date
    ) AS prev_close,
    ROUND(
        close_price - LAG(close_price) OVER (
            PARTITION BY ticker ORDER BY trade_date
        ), 4
    ) AS daily_change,
    ROUND(
        (close_price - LAG(close_price) OVER (
            PARTITION BY ticker ORDER BY trade_date
        )) / LAG(close_price) OVER (
            PARTITION BY ticker ORDER BY trade_date
        ) * 100, 2
    ) AS pct_change
FROM market_data
WHERE ticker IN ('AAPL', 'MSFT')
ORDER BY ticker, trade_date DESC;

WITH daily_buys AS (
    SELECT
        trade_date,
        ticker,
        SUM(quantity) AS bought_qty,
        SUM(notional) AS bought_notional,
        ROUND(SUM(notional) / NULLIF(SUM(quantity), 0), 4) AS avg_buy_price
    FROM trades
    WHERE side = 'BUY'
    GROUP BY trade_date, ticker
),
daily_sells AS (
    SELECT
        trade_date,
        ticker,
        SUM(quantity) AS sold_qty,
        SUM(notional) AS sold_notional,
        ROUND(SUM(notional) / NULLIF(SUM(quantity), 0), 4) AS avg_sell_price
    FROM trades
    WHERE side = 'SELL'
    GROUP BY trade_date, ticker
)
SELECT
    COALESCE(b.trade_date, s.trade_date) AS trade_date,
    COALESCE(b.ticker, s.ticker) AS ticker,
    COALESCE(b.bought_qty, 0) AS bought,
    COALESCE(s.sold_qty, 0) AS sold,
    COALESCE(b.bought_qty, 0) - COALESCE(s.sold_qty, 0) AS net_quantity,
    COALESCE(b.bought_notional, 0) AS buy_value,
    COALESCE(s.sold_notional, 0) AS sell_value,
    COALESCE(s.sold_notional, 0) - COALESCE(b.bought_notional, 0) AS realized_pnl
FROM daily_buys b
FULL OUTER JOIN daily_sells s
    ON b.trade_date = s.trade_date
    AND b.ticker = s.ticker
ORDER BY trade_date, ticker;

WITH trade_flows AS (
    SELECT
        ticker,
        SUM(CASE WHEN side = 'BUY' THEN quantity ELSE -quantity END) AS net_quantity,
        SUM(CASE WHEN side = 'BUY' THEN notional ELSE -notional END) AS net_cost
    FROM trades
    GROUP BY ticker
),
valued_positions AS (
    SELECT
        tf.ticker,
        tf.net_quantity,
        ROUND(tf.net_cost / NULLIF(tf.net_quantity, 0), 4) AS avg_cost,
        m.close_price AS current_price,
        ROUND(tf.net_quantity * m.close_price, 2) AS market_value,
        ROUND(tf.net_quantity * m.close_price - tf.net_cost, 2) AS unrealized_pnl
    FROM trade_flows tf
    LEFT JOIN (
        SELECT DISTINCT ON (ticker)
            ticker, close_price
        FROM market_data
        ORDER BY ticker, trade_date DESC
    ) m ON tf.ticker = m.ticker
    WHERE tf.net_quantity != 0
)
SELECT
    ticker,
    net_quantity,
    avg_cost,
    current_price,
    market_value,
    unrealized_pnl,
    ROUND(unrealized_pnl / NULLIF(ABS(net_quantity * avg_cost), 0) * 100, 2)
        AS pnl_pct
FROM valued_positions
ORDER BY ABS(market_value) DESC;

SELECT
    ticker,
    trade_date,
    ROUND(
        SUM(price * quantity) / NULLIF(SUM(quantity), 0),
        4
    ) AS vwap,
    SUM(quantity) AS total_volume,
    MIN(price) AS low_price,
    MAX(price) AS high_price
FROM trades
WHERE trade_date = '2026-03-28'
GROUP BY ticker, trade_date
ORDER BY total_volume DESC;

-- Find trades that are more than 3 standard deviations above the mean notional
WITH trade_stats AS (
    SELECT
        AVG(notional) AS mean_notional,
        STDDEV(notional) AS std_notional
    FROM trades
)
SELECT
    t.trade_id,
    t.trade_date,
    t.trader,
    t.ticker,
    t.notional,
    ROUND((t.notional - ts.mean_notional) / ts.std_notional, 2) AS z_score
FROM trades t
CROSS JOIN trade_stats ts
WHERE t.notional > ts.mean_notional + 3 * ts.std_notional
ORDER BY t.notional DESC;

-- Hourly trading activity breakdown
SELECT
    trader,
    EXTRACT(HOUR FROM trade_time) AS hour,
    COUNT(*) AS num_trades,
    SUM(notional) AS total_notional
FROM trades
GROUP BY trader, EXTRACT(HOUR FROM trade_time)
ORDER BY trader, hour;

-- What percentage of total exposure is in each stock?
WITH total AS (
    SELECT SUM(ABS(quantity * current_price)) AS total_exposure
    FROM positions
)
SELECT
    p.ticker,
    p.quantity,
    ABS(p.quantity * p.current_price) AS exposure,
    ROUND(
        ABS(p.quantity * p.current_price) / t.total_exposure * 100,
        2
    ) AS pct_of_total
FROM positions p
CROSS JOIN total t
ORDER BY exposure DESC;

-- Essential indexes for a trades table
CREATE INDEX idx_trades_date ON trades (trade_date);
CREATE INDEX idx_trades_ticker ON trades (ticker);
CREATE INDEX idx_trades_trader ON trades (trader);
CREATE INDEX idx_trades_date_ticker ON trades (trade_date, ticker);

-- Essential indexes for market_data
CREATE INDEX idx_market_date ON market_data (trade_date);
CREATE INDEX idx_market_ticker_date ON market_data (ticker, trade_date);

EXPLAIN ANALYZE
SELECT ticker, SUM(notional)
FROM trades
WHERE trade_date = '2026-03-28'
GROUP BY ticker;