Advanced SQL Techniques for Financial Systems

Moving Past the Basics

If you can write basic queries — SELECT, WHERE, JOIN, GROUP BY — you are functional. But "functional" and "effective" are not the same thing. The gap between a junior and a senior when it comes to SQL usually comes down to three areas: CTEs for readability, window functions for analytics, and understanding query plans for performance.

---

CTEs: Making Complex Queries Readable

A Common Table Expression (CTE) lets you name a subquery and reference it later. It is the SQL equivalent of breaking a long function into well-named smaller ones.

WITH daily_pnl AS (
    SELECT
        trade_date,
        symbol,
        SUM((exit_price - entry_price) * quantity) AS pnl
    FROM trades
    WHERE status = 'CLOSED'
    GROUP BY trade_date, symbol
),
rolling_stats AS (
    SELECT
        trade_date,
        symbol,
        pnl,
        AVG(pnl) OVER (
            PARTITION BY symbol
            ORDER BY trade_date
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS rolling_avg_pnl
    FROM daily_pnl
)
SELECT *
FROM rolling_stats
WHERE pnl > rolling_avg_pnl * 2
ORDER BY trade_date;

Without CTEs, this would be a deeply nested subquery mess. With them, each step has a name and a clear purpose. In finance, where compliance teams and auditors may need to understand your queries, readability is not optional.

Recursive CTEs

For hierarchical data — organisational structures, category trees — recursive CTEs are powerful:

WITH RECURSIVE reporting_chain AS (
    SELECT employee_id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL  -- Start from the top
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, rc.depth + 1
    FROM employees e
    JOIN reporting_chain rc ON e.manager_id = rc.employee_id
)
SELECT * FROM reporting_chain ORDER BY depth, name;

---

Window Functions in Depth

Touched on in our SQL fundamentals guide, window functions deserve a deep dive because they come up constantly in financial analytics.

LAG and LEAD: Comparing Across Time

-- Detect gaps in trading activity
SELECT
    symbol,
    trade_date,
    LAG(trade_date) OVER (PARTITION BY symbol ORDER BY trade_date) AS prev_trade_date,
    trade_date - LAG(trade_date) OVER (
        PARTITION BY symbol ORDER BY trade_date
    ) AS days_gap
FROM trades
WHERE days_gap > 5;  -- Flag symbols with unusual gaps

Running Totals and Percentages

-- Each trade as a percentage of daily volume
SELECT
    symbol,
    trade_date,
    quantity,
    SUM(quantity) OVER (PARTITION BY symbol, trade_date) AS daily_total,
    ROUND(
        100.0 * quantity / SUM(quantity) OVER (PARTITION BY symbol, trade_date),
        2
    ) AS pct_of_daily_volume
FROM trades;

-- Running P&L with drawdown tracking
SELECT
    trade_date,
    daily_pnl,
    SUM(daily_pnl) OVER (ORDER BY trade_date) AS cumulative_pnl,
    MAX(SUM(daily_pnl) OVER (ORDER BY trade_date))
        OVER (ORDER BY trade_date) AS peak_pnl
FROM daily_results;

NTILE: Bucketing Data

-- Divide trades into quartiles by size
SELECT
    trade_id,
    notional,
    NTILE(4) OVER (ORDER BY notional) AS size_quartile
FROM trades;

---

Query Performance

Writing correct SQL is step one. Writing fast SQL is step two — and in production systems processing millions of trades, performance determines whether your reports take seconds or hours.

Indexes

The single biggest performance lever. An index lets the database find specific rows without scanning the entire table:

-- Most common query pattern: symbol + date range
CREATE INDEX idx_trades_symbol_date ON trades (symbol, trade_date);

-- For account-based lookups
CREATE INDEX idx_trades_account ON trades (account_id, trade_date);

-- Partial index: only index what you actually query
CREATE INDEX idx_large_trades ON trades ((quantity * price))
WHERE quantity * price > 1000000;

Reading EXPLAIN Plans

EXPLAIN ANALYZE
SELECT * FROM trades
WHERE symbol = 'AAPL' AND trade_date >= '2024-01-01';

The output tells you: is the database using your index (good) or doing a sequential scan (usually bad for large tables)? Learning to read EXPLAIN output is one of the most practically valuable database skills.

Common Performance Pitfalls

• SELECT * in production — only select the columns you need
• Functions in WHERE clauses — WHERE YEAR(trade_date) = 2024 prevents index use; use WHERE trade_date >= '2024-01-01' instead
• IN with large subqueries — use EXISTS instead for better performance
• Missing indexes on JOIN columns — every foreign key should have an index

---

Transactions and Data Integrity

In financial systems, a half-completed operation can be catastrophic. Transactions ensure a group of operations either all succeed or all fail:

BEGIN TRANSACTION;

UPDATE positions SET quantity = quantity - 100 WHERE symbol = 'AAPL' AND account_id = 42;
INSERT INTO trades (symbol, quantity, price, side, account_id)
    VALUES ('AAPL', 100, 150.25, 'SELL', 42);
UPDATE cash_balances SET amount = amount + (100 * 150.25) WHERE account_id = 42;

COMMIT;
-- If ANY statement fails, ROLLBACK undoes everything

This atomicity is the "A" in ACID and is fundamental to database design in any system where data correctness matters — which in finance is essentially every system.

For high-throughput scenarios, understanding how to design data pipelines that work efficiently with transactions is critical knowledge.