-- Running totals and moving averages
SELECT 
    transaction_date,
    amount,
    SUM(amount) OVER (ORDER BY transaction_date) AS running_total,
    AVG(amount) OVER (ORDER BY transaction_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30,
    COUNT(*) OVER (ORDER BY transaction_date) AS transaction_count
FROM transactions
WHERE account_id = 123;

-- Ranking and row numbers
SELECT 
    symbol,
    price,
    ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY price DESC) AS rank_by_price,
    RANK() OVER (PARTITION BY symbol ORDER BY price DESC) AS rank_with_gaps,
    DENSE_RANK() OVER (PARTITION BY symbol ORDER BY price DESC) AS dense_rank
FROM price_history
WHERE timestamp >= CURRENT_DATE - INTERVAL '30 days';

-- LAG and LEAD for period-over-period analysis
SELECT 
    symbol,
    date,
    close,
    LAG(close, 1) OVER (PARTITION BY symbol ORDER BY date) AS prev_close,
    LAG(close, 5) OVER (PARTITION BY symbol ORDER BY date) AS close_5_days_ago,
    (close - LAG(close, 1) OVER (PARTITION BY symbol ORDER BY date)) / LAG(close, 1) OVER (PARTITION BY symbol ORDER BY date) AS daily_return,
    (close - LAG(close, 5) OVER (PARTITION BY symbol ORDER BY date)) / LAG(close, 5) OVER (PARTITION BY symbol ORDER BY date) AS return_5d
FROM daily_summary
WHERE instrument_id = 1001
ORDER BY date DESC;

-- Cumulative P&L by trader
WITH daily_pnl AS (
    SELECT 
        trader_id,
        trade_date,
        SUM(pnl) AS daily_pnl
    FROM trades
    GROUP BY trader_id, trade_date
)
SELECT 
    trader_id,
    trade_date,
    daily_pnl,
    SUM(daily_pnl) OVER (PARTITION BY trader_id ORDER BY trade_date) AS cumulative_pnl,
    AVG(daily_pnl) OVER (PARTITION BY trader_id ORDER BY trade_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS ma_20_pnl
FROM daily_pnl;

-- Drawdown calculation
WITH equity_curve AS (
    SELECT 
        date,
        SUM(pnl) OVER (ORDER BY date) AS equity
    FROM daily_pnl
),
drawdown AS (
    SELECT 
        date,
        equity,
        MAX(equity) OVER (ORDER BY date) AS peak_equity,
        (equity / MAX(equity) OVER (ORDER BY date) - 1) * 100 AS drawdown_pct
    FROM equity_curve
)
SELECT *
FROM drawdown
WHERE drawdown_pct < -5;  -- Find drawdowns > 5%

-- Daily, monthly, quarterly aggregations
SELECT 
    DATE_TRUNC('day', transaction_date) AS day,
    DATE_TRUNC('month', transaction_date) AS month,
    DATE_TRUNC('quarter', transaction_date) AS quarter,
    SUM(amount) AS total_amount,
    COUNT(*) AS transaction_count
FROM transactions
WHERE account_id = 123
GROUP BY ROLLUP(day, month, quarter)
ORDER BY day, month, quarter;

-- Year-over-year comparison
SELECT 
    EXTRACT(YEAR FROM transaction_date) AS year,
    EXTRACT(MONTH FROM transaction_date) AS month,
    SUM(amount) AS current_month_amount,
    LAG(SUM(amount), 12) OVER (ORDER BY EXTRACT(YEAR FROM transaction_date), EXTRACT(MONTH FROM transaction_date)) AS previous_year_amount,
    (SUM(amount) - LAG(SUM(amount), 12) OVER (ORDER BY EXTRACT(YEAR FROM transaction_date), EXTRACT(MONTH FROM transaction_date))) 
        / LAG(SUM(amount), 12) OVER (ORDER BY EXTRACT(YEAR FROM transaction_date), EXTRACT(MONTH FROM transaction_date)) AS yoy_growth
FROM transactions
GROUP BY EXTRACT(YEAR FROM transaction_date), EXTRACT(MONTH FROM transaction_date)
ORDER BY year, month;

-- Time-series filling (generate missing dates)
WITH date_series AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-12-31'::date,
        '1 day'::interval
    )::date AS date
)
SELECT 
    ds.date,
    COALESCE(ds_agg.daily_volume, 0) AS daily_volume,
    COALESCE(ds_agg.transaction_count, 0) AS transaction_count
FROM date_series ds
LEFT JOIN (
    SELECT 
        transaction_date::date AS date,
        SUM(amount) AS daily_volume,
        COUNT(*) AS transaction_count
    FROM transactions
    GROUP BY transaction_date::date
) ds_agg ON ds.date = ds_agg.date
ORDER BY ds.date;

-- Month-over-month (MoM) growth with window functions
WITH monthly_volume AS (
    SELECT 
        DATE_TRUNC('month', transaction_date) AS month,
        SUM(amount) AS total_volume
    FROM transactions
    GROUP BY DATE_TRUNC('month', transaction_date)
)
SELECT 
    month,
    total_volume,
    LAG(total_volume) OVER (ORDER BY month) AS previous_month_volume,
    (total_volume - LAG(total_volume) OVER (ORDER BY month)) / LAG(total_volume) OVER (ORDER BY month) AS mom_growth,
    (total_volume / LAG(total_volume, 12) OVER (ORDER BY month) - 1) AS yoy_growth
FROM monthly_volume
ORDER BY month DESC;

-- Rolling 30-day metrics
SELECT 
    transaction_date,
    SUM(amount) OVER (ORDER BY transaction_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30d_volume,
    COUNT(*) OVER (ORDER BY transaction_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30d_count,
    AVG(amount) OVER (ORDER BY transaction_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30d_avg_amount
FROM transactions
WHERE account_id = 123
ORDER BY transaction_date DESC;

-- Portfolio performance by instrument
WITH position_pnl AS (
    SELECT 
        p.account_id,
        p.instrument_id,
        i.symbol,
        p.quantity,
        p.average_cost,
        p.current_price,
        (p.current_price - p.average_cost) * p.quantity AS unrealized_pnl
    FROM positions p
    JOIN instruments i ON p.instrument_id = i.instrument_id
    WHERE p.account_id = 123
),
portfolio_summary AS (
    SELECT 
        SUM(p.quantity * p.current_price) AS total_value,
        SUM(p.quantity * p.average_cost) AS total_cost_basis,
        SUM((p.current_price - p.average_cost) * p.quantity) AS total_unrealized_pnl,
        (SUM((p.current_price - p.average_cost) * p.quantity) / SUM(p.quantity * p.average_cost)) * 100 AS pnl_pct
    FROM position_pnl
)
SELECT 
    symbol,
    quantity,
    average_cost,
    current_price,
    unrealized_pnl,
    unrealized_pnl * 100.0 / (average_cost * quantity) AS return_pct,
    quantity * current_price AS market_value,
    (quantity * current_price) * 100.0 / (SELECT total_value FROM portfolio_summary) AS weight_pct
FROM position_pnl
ORDER BY market_value DESC;

-- Performance attribution
WITH daily_returns AS (
    SELECT 
        d.trade_date,
        i.symbol,
        d.close / LAG(d.close) OVER (PARTITION BY i.instrument_id ORDER BY d.trade_date) - 1 AS return_pct
    FROM daily_summary d
    JOIN instruments i ON d.instrument_id = i.instrument_id
)
SELECT 
    symbol,
    AVG(return_pct) * 252 AS avg_annual_return,
    STDDEV(return_pct) * SQRT(252) AS annual_volatility,
    AVG(return_pct) * 252 / (STDDEV(return_pct) * SQRT(252)) AS sharpe_ratio,
    MIN(return_pct) AS max_daily_loss,
    MAX(return_pct) AS max_daily_gain
FROM daily_returns
WHERE trade_date >= CURRENT_DATE - INTERVAL '252 days'
GROUP BY symbol
ORDER BY avg_annual_return DESC;

-- Historical VaR calculation
WITH daily_returns AS (
    SELECT 
        instrument_id,
        trade_date,
        (close / LAG(close) OVER (PARTITION BY instrument_id ORDER BY trade_date) - 1) AS return_pct
    FROM daily_summary
)
SELECT 
    instrument_id,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY return_pct) AS var_95,
    PERCENTILE_CONT(0.01) WITHIN GROUP (ORDER BY return_pct) AS var_99,
    AVG(return_pct) AS avg_return,
    STDDEV(return_pct) AS std_dev
FROM daily_returns
WHERE trade_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY instrument_id;

-- Portfolio VaR (requires covariance matrix)
WITH instrument_returns AS (
    SELECT 
        instrument_id,
        trade_date,
        (close / LAG(close) OVER (PARTITION BY instrument_id ORDER BY trade_date) - 1) AS return_pct
    FROM daily_summary
    WHERE trade_date >= CURRENT_DATE - INTERVAL '1 year'
),
correlation_matrix AS (
    SELECT 
        a.instrument_id AS instrument_a,
        b.instrument_id AS instrument_b,
        CORR(a.return_pct, b.return_pct) AS correlation
    FROM instrument_returns a
    JOIN instrument_returns b ON a.trade_date = b.trade_date
    GROUP BY a.instrument_id, b.instrument_id
)
SELECT 
    instrument_a,
    instrument_b,
    correlation
FROM correlation_matrix
WHERE instrument_a < instrument_b;

-- Identify unusual transaction patterns
WITH transaction_stats AS (
    SELECT 
        account_id,
        transaction_date,
        amount,
        AVG(amount) OVER (PARTITION BY account_id) AS avg_amount,
        STDDEV(amount) OVER (PARTITION BY account_id) AS std_amount
    FROM transactions
    WHERE transaction_type IN ('DEPOSIT', 'WITHDRAWAL')
)
SELECT 
    account_id,
    transaction_date,
    amount,
    avg_amount,
    std_amount,
    (amount - avg_amount) / std_amount AS z_score
FROM transaction_stats
WHERE (amount - avg_amount) / std_amount > 3  -- Outliers > 3 standard deviations
ORDER BY z_score DESC;

-- Detect circular transactions (potential money laundering)
WITH transfers AS (
    SELECT 
        t.transaction_id,
        t.account_id as from_account,
        t.related_transaction_id,
        t2.account_id as to_account,
        t.amount,
        t.transaction_date
    FROM transactions t
    LEFT JOIN transactions t2 ON t.related_transaction_id = t2.transaction_id
    WHERE t.transaction_type IN ('TRANSFER_IN', 'TRANSFER_OUT')
),
circular_pattern AS (
    SELECT 
        t1.from_account,
        t1.to_account,
        t1.amount,
        t1.transaction_date,
        t2.to_account AS second_hop,
        t3.to_account AS third_hop
    FROM transfers t1
    JOIN transfers t2 ON t1.to_account = t2.from_account AND t2.transaction_date > t1.transaction_date
    JOIN transfers t3 ON t2.to_account = t3.from_account AND t3.transaction_date > t2.transaction_date
    WHERE t1.from_account = t3.to_account  -- Circular pattern detected
)
SELECT * FROM circular_pattern;

-- Materialized view for portfolio summary
CREATE MATERIALIZED VIEW portfolio_summary AS
WITH positions_agg AS (
    SELECT 
        p.account_id,
        p.instrument_id,
        i.symbol,
        i.instrument_type,
        i.sector,
        p.quantity,
        p.average_cost,
        p.current_price,
        (p.current_price - p.average_cost) * p.quantity AS unrealized_pnl
    FROM positions p
    JOIN instruments i ON p.instrument_id = i.instrument_id
)
SELECT 
    account_id,
    SUM(quantity * current_price) AS total_value,
    SUM(quantity * average_cost) AS total_cost_basis,
    SUM(unrealized_pnl) AS total_unrealized_pnl,
    SUM(quantity * current_price) / SUM(quantity * average_cost) - 1 AS total_return_pct,
    COUNT(DISTINCT instrument_id) AS unique_positions
FROM positions_agg
GROUP BY account_id;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW portfolio_summary;

-- Materialized view for daily performance
CREATE MATERIALIZED VIEW daily_performance AS
SELECT 
    d.trade_date,
    p.account_id,
    SUM(p.quantity * p.current_price) AS total_value,
    SUM(p.quantity * p.average_cost) AS cost_basis,
    SUM((p.current_price - p.average_cost) * p.quantity) AS pnl
FROM daily_summary d
JOIN positions p ON d.instrument_id = p.instrument_id
WHERE d.trade_date = CURRENT_DATE
GROUP BY d.trade_date, p.account_id;

-- Create indexes for performance
CREATE INDEX idx_transactions_account_date ON transactions(account_id, transaction_date DESC);
CREATE INDEX idx_transactions_type_date ON transactions(transaction_type, transaction_date);
CREATE INDEX idx_orders_account_status ON orders(account_id, status);
CREATE INDEX idx_orders_symbol_status ON orders(instrument_id, status);
CREATE INDEX idx_positions_account_instrument ON positions(account_id, instrument_id);
CREATE INDEX idx_price_history_symbol_date ON price_history(instrument_id, timestamp DESC);

-- Partial index for active orders
CREATE INDEX idx_orders_active ON orders(account_id, instrument_id) WHERE status = 'PENDING';

-- Multi-column index for portfolio queries
CREATE INDEX idx_positions_analysis ON positions(account_id, instrument_id, quantity, average_cost) 
WHERE quantity > 0;

-- Covering index for daily summary
CREATE INDEX idx_daily_summary_analysis ON daily_summary(instrument_id, trade_date) 
INCLUDE (open, high, low, close, volume);

-- Load price data from CSV
COPY daily_summary (instrument_id, trade_date, open, high, low, close, volume)
FROM '/data/price_data.csv'
DELIMITER ','
CSV HEADER;

-- Load with transformation using staging table
CREATE TEMP TABLE temp_price_data (
    symbol VARCHAR(20),
    trade_date DATE,
    open DECIMAL(18,4),
    high DECIMAL(18,4),
    low DECIMAL(18,4),
    close DECIMAL(18,4),
    volume INTEGER
);

COPY temp_price_data FROM '/data/price_data.csv' DELIMITER ',' CSV HEADER;

INSERT INTO daily_summary (instrument_id, trade_date, open, high, low, close, volume)
SELECT 
    i.instrument_id,
    t.trade_date,
    t.open,
    t.high,
    t.low,
    t.close,
    t.volume
FROM temp_price_data t
JOIN instruments i ON t.symbol = i.symbol
ON CONFLICT (instrument_id, trade_date) DO UPDATE SET
    open = EXCLUDED.open,
    high = EXCLUDED.high,
    low = EXCLUDED.low,
    close = EXCLUDED.close,
    volume = EXCLUDED.volume;

-- Incremental load with change tracking
CREATE TABLE cdc_changes (
    change_id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    record_id INTEGER,
    operation VARCHAR(20),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed BOOLEAN DEFAULT FALSE
);

-- Trigger for change tracking
CREATE OR REPLACE FUNCTION track_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO cdc_changes (table_name, record_id, operation)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT');
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO cdc_changes (table_name, record_id, operation)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE');
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO cdc_changes (table_name, record_id, operation)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE');
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger to table
CREATE TRIGGER track_orders_changes
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION track_changes();

-- Explain plan for a query
EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE)
SELECT 
    o.order_id,
    o.account_id,
    o.symbol,
    o.quantity,
    o.filled_quantity,
    e.execution_price
FROM orders o
LEFT JOIN executions e ON o.order_id = e.order_id
WHERE o.account_id = 123
AND o.status = 'PENDING'
ORDER BY o.created_at DESC;

-- Statistics for optimization
ANALYZE orders;
ANALYZE executions;

-- Vacuum for space reclamation
VACUUM ANALYZE orders;
VACUUM FULL orders;

-- Before: Slow query with multiple subqueries
SELECT 
    account_id,
    (SELECT SUM(amount) FROM transactions WHERE account_id = a.account_id AND transaction_type = 'DEPOSIT') AS total_deposits,
    (SELECT SUM(amount) FROM transactions WHERE account_id = a.account_id AND transaction_type = 'WITHDRAWAL') AS total_withdrawals
FROM accounts a;

-- After: Optimized with joins
WITH account_transactions AS (
    SELECT 
        account_id,
        SUM(CASE WHEN transaction_type = 'DEPOSIT' THEN amount ELSE 0 END) AS total_deposits,
        SUM(CASE WHEN transaction_type = 'WITHDRAWAL' THEN amount ELSE 0 END) AS total_withdrawals
    FROM transactions
    GROUP BY account_id
)
SELECT 
    a.account_id,
    COALESCE(at.total_deposits, 0) AS total_deposits,
    COALESCE(at.total_withdrawals, 0) AS total_withdrawals
FROM accounts a
LEFT JOIN account_transactions at ON a.account_id = at.account_id;