Daily Returns and Price Changes

The daily return is the most fundamental calculation in finance. It answers: "By what percentage did this stock move today?" Returns are the raw material for nearly every other financial metric — volatility, Sharpe ratio, correlation, momentum signals. Understanding how to compute them correctly in SQL is essential.

What Is a Daily Return?

A daily return measures the percentage change in price from one day to the next:

Daily Return = (Today's Close - Yesterday's Close) / Yesterday's Close × 100

Or equivalently, using the ratio method (preferred in quantitative finance):

Daily Return = (Today's Close / Yesterday's Close) - 1

The ratio method is preferred because it compounds correctly over multiple periods. A 10% gain followed by a 10% loss does not return you to the starting price (it leaves you at 99% of the original), and the ratio method captures this accurately.

Step 1 — Compute Daily Returns Using LAG()

LAG() is the SQL tool for accessing the previous row's value. LAG(close_price) OVER (PARTITION BY company_id ORDER BY price_date) fetches the prior trading day's close for the same stock.

Notice PARTITION BY company_id — without this, the "previous row" for AAPL on Monday might be MSFT's Friday close, which would produce completely wrong returns.

WITH daily_returns AS (
    SELECT
        company_id,
        price_date,
        close_price,
                LAG(close_price) OVER (
            PARTITION BY company_id
            ORDER BY price_date
        ) AS prev_close_price,
        ROUND(((close_price - LAG(close_price) OVER (
                PARTITION BY company_id ORDER BY price_date
            )) / NULLIF(LAG(close_price) OVER (
                PARTITION BY company_id ORDER BY price_date
            ), 0) * 100)::NUMERIC, 4) AS daily_return_pct
    FROM stock_prices
)
SELECT
    c.ticker,
    dr.price_date,
    dr.close_price,
    dr.prev_close_price,
    dr.daily_return_pct
FROM daily_returns dr
JOIN companies c ON c.company_id = dr.company_id
WHERE dr.daily_return_pct IS NOT NULL  -- first row per stock has no prior day
ORDER BY c.ticker, dr.price_date;

What This Returns

Each row shows one stock on one day with its return. The daily_return_pct IS NOT NULL filter removes the first row for each stock (which has no prior day to compare to, making LAG() return NULL). A daily_return_pct of 2.5 means the stock gained 2.5% that day.

Why close_price? If a stock pays a $1 dividend on its $100 close price, the next day's adjusted open will be $99 — but the unadjusted close was still $100. If you use raw close prices, you'd calculate a false 1% loss. Adjusted close removes this distortion.