SQL for financial data

Key takeaways

• Store prices in a long table keyed by security and date, not one column per security; it scales and every query pattern works on it.
• GROUP BY collapses rows into one per group; window functions (OVER) compute across rows while keeping every row, which is what returns and rankings need.
• LAG(price) OVER (PARTITION BY security ORDER BY date) gives the previous price, so a daily return is one expression.
• SUM(...) OVER with an ordered window gives running totals, such as a position built from a trade blotter.
• Point in time joins pick the latest record published on or before each date, usually with a LATERAL subquery; joining on period end dates leaks future information.

A schema for prices and holdings

Start with three tables. securities holds one row per instrument with its identifiers and descriptive fields. prices holds one row per security per date: a long, narrow table rather than a wide one with a column per ticker. holdings holds one row per portfolio, security and as of date, with the number of shares. Keys enforce that each fact appears once, and foreign keys stop a price or holding from referring to a security that does not exist.

Use exact numeric types for money and quantities. Floating point types round in binary and accumulate small errors in sums; numeric does not. Store dates as date, not text, so comparisons and date arithmetic work. And use an internal integer security_id as the key rather than the ticker, because tickers change and are reused.

CREATE TABLE securities (
  security_id  integer PRIMARY KEY,
  ticker       text    NOT NULL,
  name         text    NOT NULL,
  sector       text
);

CREATE TABLE prices (
  security_id  integer        NOT NULL REFERENCES securities (security_id),
  price_date   date           NOT NULL,
  close_price  numeric(12, 4) NOT NULL,
  PRIMARY KEY (security_id, price_date)
);

CREATE TABLE holdings (
  portfolio_id text           NOT NULL,
  security_id  integer        NOT NULL REFERENCES securities (security_id),
  as_of_date   date           NOT NULL,
  shares       numeric(18, 4) NOT NULL,
  PRIMARY KEY (portfolio_id, security_id, as_of_date)
);

INSERT INTO securities VALUES
  (1, 'AAA', 'Alpha Industrials', 'Industrials'),
  (2, 'BBB', 'Beta Retail', 'Consumer');

INSERT INTO prices VALUES
  (1, '2024-01-02', 50.00), (1, '2024-01-03', 51.00),
  (1, '2024-01-04', 50.49), (1, '2024-01-05', 52.00),
  (2, '2024-01-02', 20.00), (2, '2024-01-03', 19.80),
  (2, '2024-01-04', 20.10), (2, '2024-01-05', 20.30);

Joins and GROUP BY

A join combines rows from two tables where a condition holds. To value holdings you need each holding's shares, the price on the same date and the security's sector: holdings joined to prices on security and date, and to securities on security. An inner join keeps only rows that match on both sides, so a holding with no price that day silently disappears; a LEFT JOIN keeps it with a null price, which is usually what you want in a report, because a missing price is something to investigate rather than hide.

GROUP BY collapses rows into one per group and computes aggregates over each: SUM, COUNT, AVG, MIN, MAX. Sector exposure per portfolio is a group by portfolio and sector with a sum of market value. HAVING filters on the aggregated result, the way WHERE filters on individual rows.

SELECT
  h.portfolio_id,
  s.sector,
  COUNT(*)                                AS positions,
  ROUND(SUM(h.shares * p.close_price), 2) AS market_value
FROM holdings h
JOIN securities s
  ON s.security_id = h.security_id
LEFT JOIN prices p
  ON p.security_id = h.security_id
 AND p.price_date  = h.as_of_date
WHERE h.as_of_date = DATE '2024-01-05'
GROUP BY h.portfolio_id, s.sector
HAVING SUM(h.shares * p.close_price) > 0
ORDER BY h.portfolio_id, market_value DESC;

Window functions: returns and running totals

A window function computes a value for each row from a set of related rows, without collapsing them. The OVER clause defines the set: PARTITION BY splits rows into groups (one per security), and ORDER BY sorts within each group. LAG(close_price) then returns the previous row's price within the same security, so the daily return is simply the current price over the lagged one, minus one. FIRST_VALUE gives the first price in the partition, so the cumulative return since the start is the same idea.

Running totals use SUM as a window function with an ordered frame. A trade blotter records buys as positive quantities and sells as negative; the running sum in date order is the position after each trade. Name the window once with a WINDOW clause when several expressions share it, which keeps the query readable.

SELECT
  s.ticker,
  p.price_date,
  p.close_price,
  ROUND(p.close_price / LAG(p.close_price)         OVER w - 1, 4) AS daily_return,
  ROUND(p.close_price / FIRST_VALUE(p.close_price) OVER w - 1, 4) AS cum_return
FROM prices p
JOIN securities s ON s.security_id = p.security_id
WINDOW w AS (PARTITION BY p.security_id ORDER BY p.price_date)
ORDER BY s.ticker, p.price_date;

 ticker | price_date | close_price | daily_return | cum_return
--------+------------+-------------+--------------+------------
 AAA    | 2024-01-02 |     50.0000 |              |     0.0000
 AAA    | 2024-01-03 |     51.0000 |       0.0200 |     0.0200
 AAA    | 2024-01-04 |     50.4900 |      -0.0100 |     0.0098
 AAA    | 2024-01-05 |     52.0000 |       0.0299 |     0.0400
 BBB    | 2024-01-02 |     20.0000 |              |     0.0000
 BBB    | 2024-01-03 |     19.8000 |      -0.0100 |    -0.0100
 BBB    | 2024-01-04 |     20.1000 |       0.0152 |     0.0050
 BBB    | 2024-01-05 |     20.3000 |       0.0100 |     0.0150
(8 rows)

CREATE TABLE trades (
  trade_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  portfolio_id text           NOT NULL,
  security_id  integer        NOT NULL REFERENCES securities (security_id),
  trade_date   date           NOT NULL,
  quantity     numeric(18, 4) NOT NULL   -- buys positive, sells negative
);

SELECT
  portfolio_id,
  security_id,
  trade_date,
  quantity,
  SUM(quantity) OVER (
    PARTITION BY portfolio_id, security_id
    ORDER BY trade_date, trade_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS position_after_trade
FROM trades
ORDER BY portfolio_id, security_id, trade_date, trade_id;

Ranking top holdings

Ranking is another window function job. RANK() OVER (PARTITION BY portfolio ORDER BY market value DESC) numbers each portfolio's positions from largest to smallest. ROW_NUMBER gives a strict sequence even when values tie; RANK gives tied values the same number and skips the next; DENSE_RANK ties without skipping. A position's weight is its market value divided by the portfolio total, which a SUM window over the partition provides without a separate query.

Common table expressions (WITH clauses) keep multi-step queries readable: one step values the positions, the next ranks and weights them, the last filters to the top ten. Window functions cannot appear in WHERE, because they are computed after filtering, so the rank must be computed in one step and filtered in the next.

WITH valued AS (
  SELECT
    h.portfolio_id,
    s.ticker,
    h.shares * p.close_price AS market_value
  FROM holdings h
  JOIN prices p
    ON p.security_id = h.security_id
   AND p.price_date  = h.as_of_date
  JOIN securities s
    ON s.security_id = h.security_id
  WHERE h.as_of_date = DATE '2024-01-05'
),
ranked AS (
  SELECT
    portfolio_id,
    ticker,
    market_value,
    market_value / SUM(market_value) OVER (PARTITION BY portfolio_id) AS weight,
    RANK() OVER (PARTITION BY portfolio_id ORDER BY market_value DESC) AS rnk
  FROM valued
)
SELECT
  portfolio_id,
  rnk,
  ticker,
  ROUND(market_value, 2)  AS market_value,
  ROUND(100 * weight, 2)  AS weight_pct
FROM ranked
WHERE rnk <= 10
ORDER BY portfolio_id, rnk;

Point in time joins

Research needs data as it was known on each date, not as it is known now. Fundamentals are the standard case: a quarter's figures are published weeks after the quarter ends, and may be restated later. Store each version with both the period it describes (period_end) and the date it became public (reported_at). A point in time join then selects, for each price date, the most recent row with reported_at on or before that date.

In PostgreSQL the cleanest form is a LATERAL subquery: for each outer row, run a small query that filters to the security, keeps rows reported by that date, orders by reported_at descending and takes the first. With an index on (security_id, reported_at), each lookup is fast. The same pattern finds the latest price on or before a weekend holdings date, the index membership as of a date, or the latest credit rating.

CREATE TABLE fundamentals (
  security_id          integer        NOT NULL REFERENCES securities (security_id),
  period_end           date           NOT NULL,
  reported_at          date           NOT NULL,
  book_value_per_share numeric(12, 4) NOT NULL,
  PRIMARY KEY (security_id, period_end, reported_at)
);

CREATE INDEX fundamentals_pit_idx
  ON fundamentals (security_id, reported_at DESC);

SELECT
  p.security_id,
  p.price_date,
  p.close_price,
  f.period_end,
  f.reported_at,
  ROUND(f.book_value_per_share / p.close_price, 4) AS book_to_price
FROM prices p
LEFT JOIN LATERAL (
  SELECT fu.period_end, fu.reported_at, fu.book_value_per_share
  FROM fundamentals fu
  WHERE fu.security_id = p.security_id
    AND fu.reported_at <= p.price_date
  ORDER BY fu.reported_at DESC, fu.period_end DESC
  LIMIT 1
) f ON true
ORDER BY p.security_id, p.price_date;

Templates for this lesson

Included with a Premieur membership.

• SQLFinancial data schema and queriesA PostgreSQL schema for securities, prices, holdings, trades and FX, with sample rows and commented queries for returns, value, rankings and drawdowns.Download