Stock Correlation Matrix in One SQL Query

A stock correlation matrix is a grid showing how closely each pair of tickers moved together, and one SQL query returns the whole grid: turn closing prices into daily returns, join every ticker to every other ticker on matching dates, then run a correlation over each pair. The query below does that for six large caps across the twelve months ending September 2026. The rest of this page covers what the usual tutorial version of that calculation gets wrong.

The one query that returns a stock correlation matrix

Correlation measures how two series move relative to their own averages, on a scale from -1 to +1. At +1 the two move in lockstep. At 0, knowing one move tells you nothing about the other. At -1 they move in exact opposition. A matrix is that single number computed for every pair in a list, and the long form below (one row per pair) is the shape SQL returns naturally.

The query has a structure worth learning. The first block pulls each ticker's daily close and its prior close with a window function. The second block turns that pair of prices into a daily return. The final SELECT joins the return table to itself on the date column, keeps one direction of each pair with a a.ticker < b.ticker filter, and aggregates.

WITH
    daily AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close) AS px,
            lagInFrame(toFloat64(close), 1) OVER (
                PARTITION BY ticker ORDER BY date
                ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
            ) AS prev_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'XOM', 'KO', 'PG')
          AND date >= '2025-10-01'
          AND date <  '2026-10-01'
    ),
    rets AS
    (
        SELECT
            ticker,
            date,
            px / prev_px - 1 AS ret
        FROM daily
        WHERE prev_px > 0
    )
SELECT
    concat(a.ticker, ' / ', b.ticker) AS pair,
    round(corr(a.ret, b.ret), 3)      AS return_corr
FROM rets AS a
INNER JOIN rets AS b ON a.date = b.date
WHERE a.ticker < b.ticker
GROUP BY pair
ORDER BY return_corr DESC

Six names produce fifteen unordered pairs, and the panel returns 15 rows. The top of the ranking over this window is KO / PG at 0.524; the bottom is KO / NVDA at -0.292. Swapping the ticker list in that one line is the whole customisation.

Why a correlation matrix uses returns, not price levels

The most common error in a published matrix is correlating price levels. A price level carries its entire history inside it: today's close is yesterday's close plus one day of movement. Two stocks that both drifted upward over a year will show a large correlation of levels even when their individual daily moves had little in common. The term for that is spurious correlation: honest arithmetic applied to the wrong variable.

The panel below runs both statistics over the same pairs and the same sessions, sorted by the distance between them.

WITH
    daily AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close) AS px,
            lagInFrame(toFloat64(close), 1) OVER (
                PARTITION BY ticker ORDER BY date
                ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
            ) AS prev_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'XOM', 'KO', 'PG')
          AND date >= '2025-10-01'
          AND date <  '2026-10-01'
    ),
    series AS
    (
        SELECT
            ticker,
            date,
            px,
            px / prev_px - 1 AS ret
        FROM daily
        WHERE prev_px > 0
    )
SELECT
    concat(a.ticker, ' / ', b.ticker)               AS pair,
    round(corr(a.px, b.px), 3)                      AS price_level_corr,
    round(corr(a.ret, b.ret), 3)                    AS return_corr,
    round(corr(a.px, b.px) - corr(a.ret, b.ret), 3) AS corr_gap
FROM series AS a
INNER JOIN series AS b ON a.date = b.date
WHERE a.ticker < b.ticker
GROUP BY pair
ORDER BY corr_gap DESC

The widest split sits at KO / NVDA. Price levels correlate 0.692 while the daily returns of those same two names correlate -0.292, a distance of 0.984 between two numbers that a careless write-up would both call "the correlation". Read further down and the gap changes sign, which is the point: the level number is not an inflated version of the return number, it is a different measurement, and there is no adjustment that converts one into the other.

Trading days have to line up before you correlate

Correlation consumes paired observations. The join on the date column is what makes them paired, and it is also the step that fails quietly. When a name is halted for a session, lists partway through the window, or stops trading inside it, the inner join shrinks that one pair's sample to the intersection of the two calendars. Other cells keep their full sample. The matrix then mixes estimates built on different numbers of days without labelling any of them.

The two usual workarounds are worse than the join. Forward-filling a missing close inserts a 0% return for one leg on a day the other leg moved, which pulls that pair's estimate toward zero. Aligning by row position rather than by date offsets every observation after the gap by one session, which flattens a genuinely related pair toward zero as well. Checking the calendars first costs one query.

WITH
    spy_days AS
    (
        SELECT date
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2025-10-01'
          AND date <  '2026-10-01'
    )
SELECT
    ticker,
    count()                                      AS own_sessions,
    countIf(date IN (SELECT date FROM spy_days)) AS sessions_shared_with_spy
FROM global_markets.stocks_daily_aggs
WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'XOM', 'KO', 'PG')
  AND date >= '2025-10-01'
  AND date <  '2026-10-01'
GROUP BY ticker
ORDER BY sessions_shared_with_spy ASC, ticker ASC

In this basket the check is uneventful. The least-aligned name shares 251 sessions with SPY out of its own 251 daily bars, so no cell in the matrix is quietly shorter than the others. An uneventful answer is still an answer, and it is cheaper to confirm than to assume. If you want to know what sits inside one of those bars, how OHLCV bars are built walks through the construction.

How precise is a correlation from a year of daily data?

A correlation computed from a sample is an estimate, and estimates carry error bars. The panel below splits one pair, AAPL and MSFT, into calendar quarters and measures each quarter on its own. The companies are the same in every window. Only the sample changes.

WITH
    daily AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close) AS px,
            lagInFrame(toFloat64(close), 1) OVER (
                PARTITION BY ticker ORDER BY date
                ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
            ) AS prev_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL', 'MSFT')
          AND date >= '2025-01-01'
          AND date <  '2026-10-01'
    ),
    rets AS
    (
        SELECT ticker, date, px / prev_px - 1 AS ret
        FROM daily
        WHERE prev_px > 0
    ),
    paired AS
    (
        SELECT
            a.date AS d,
            a.ret  AS ret_aapl,
            b.ret  AS ret_msft
        FROM rets AS a
        INNER JOIN rets AS b ON a.date = b.date
        WHERE a.ticker = 'AAPL' AND b.ticker = 'MSFT'
    )
SELECT
    toString(toStartOfQuarter(d))                AS window_start,
    formatDateTime(toStartOfQuarter(d), '%b %Y') AS quarter_label,
    round(corr(ret_aapl, ret_msft), 3)           AS return_corr
FROM paired
GROUP BY window_start, quarter_label
ORDER BY window_start

The first window, Jan 2025, measured 0.415. The last, Jul 2026, measured 0. Trace the line across all 7 windows and the wobble is plain.

A standard formula puts numbers on that wobble. For a correlation r estimated from n paired observations, the standard error is close to (1 - r squared) divided by the square root of (n - 1). Work it through for a year of daily data, roughly 250 observations, on a correlation of 0.65: the standard error lands near 0.04, and a 95 percent interval runs from about 0.58 to 0.72. A second pair that prints 0.71 over the same window sits inside that interval, so 0.62 and 0.71 are frequently the same number wearing different digits. Shorten the window to a single quarter, around 60 observations, and that standard error roughly doubles. Sorting matrix cells to two decimal places claims a precision the sample does not carry. The conventional repair is to compare correlations on the Fisher transform, an arctanh of r whose error is roughly constant across the scale, instead of comparing raw values.

What makes a matrix look more correlated than it is

The easiest way to manufacture a misleadingly high matrix is to restrict it to one sector over a short window. Names inside a single sector share the same broad exposures, so the pairwise numbers start high by construction, and a quarter-length window hands every cell a wide error bar at the same time. The grid then looks both tight and precise while being neither. Widening the universe across sectors and lengthening the window changes nothing in the SQL above except the ticker list and two date literals.

Measuring how exposure stacks up across a whole holding list is a separate exercise from a pairwise grid; concentration risk covers that measurement. And when two price levels really do travel together over long stretches, correlation is the wrong test entirely, which is the subject of pairs trading and cointegration.

Running the matrix query from a shell with curl and jq

The same SQL runs over HTTP. As of October 2026 the public demo endpoint accepts a GET with a sql parameter, needs no key and no signup, and returns JSON carrying a rows array of objects keyed by column name alongside columns, row_count and elapsed_ms. It caps results at 500 rows with a 20 second timeout, and the matrix query sits well inside both.

Smoke test the endpoint first: curl -sG https://ai.strasmore.com/api/demo/sql --data-urlencode "sql=SELECT ticker, count() AS sessions FROM global_markets.stocks_daily_aggs WHERE ticker IN ('AAPL','MSFT') AND date >= '2026-01-01' GROUP BY ticker" | jq .rows

Then paste the matrix SQL from the first panel into a file called corr.sql and run: curl -sG https://ai.strasmore.com/api/demo/sql --data-urlencode "[email protected]" | jq -r '.rows[] | [.pair, .return_corr] | @tsv'

The name@file form of --data-urlencode reads the file and encodes it, which keeps quotes and newlines intact, and -G moves that encoded body onto the URL as a query string. Full endpoint notes live in the free SQL API for stock market data, and the same request in a script is covered by the free stock data API in Python.

FAQ

What is a stock correlation matrix?

It is a grid of pairwise correlation values between the daily returns of a list of tickers, on a scale from -1 to +1. Each cell summarises how closely two names moved together over one fixed window of sessions.

Should a correlation matrix use prices or returns?

Returns. Correlating price levels measures shared drift across the window and can print a large value for two names whose daily moves have little in common, which is why the panel above reports both numbers side by side.

How many days of data do you need for a correlation matrix?

About a year of daily returns, roughly 250 observations, still leaves a standard error near 0.04 on a correlation of 0.65. A quarter of data roughly doubles that error, so short windows support ranking by broad bands rather than by decimals.

What does a correlation of 0 between two stocks mean?

It means that over the measured window, knowing one name's daily return gave no linear information about the other's. It says nothing about non-linear relationships, and it describes the sampled window only.

Every panel on this page carries the exact SQL beneath it. Change the six tickers and the two dates, run it in your own shell, or ask the same question in plain English on the Strasmore terminal.