Log returns

Log returns measure the relative change between consecutive prices using the natural logarithm. They are preferred over simple returns in financial analysis because they are additive over time and symmetric.

Problem​

You want to compute log returns between consecutive price observations.

Solution​

Use LAG() to access the previous row's value, then compute the natural log of the price ratio.

Tick-by-tick log returns​

This example computes log returns from the mid-price of individual quotes:

Log returns from mid-priceDemo this query
DECLARE
  @symbol := 'EURUSD',
  @lookback := '$now - 1m..$now'

SELECT
  timestamp,
  symbol,
  round((bid_price + ask_price) / 2, 5) AS mid_price,
  LN(
    (bid_price + ask_price) / 2 /
    LAG((bid_price + ask_price) / 2)
        OVER (PARTITION BY symbol ORDER BY timestamp)
  ) AS log_return
FROM core_price
WHERE symbol = @symbol
  AND ecn = 'LMAX'
  AND timestamp IN @lookback;

The core_price table contains quotes from multiple ECNs (LMAX, EBS, Currenex, Hotspot). Filtering to a single ECN avoids mixing venue-level price differences into returns - without the filter, consecutive rows may come from different venues, producing spurious returns that reflect venue spreads rather than true price moves.

Tick returns and microstructure noise

Raw tick-level log returns overstate volatility due to bid-ask bounce: even if the true price is unchanged, alternating bid- and ask-side quotes produce non-zero returns. For volatility estimation, use the fixed-frequency approach below, which aggregates ticks into bars before computing returns.

Fixed-frequency log returns​

For returns at a fixed frequency, sample first then compute returns on the sampled result:

One-minute log returnsDemo this query
DECLARE
  @symbol := 'EURUSD',
  @lookback := '$now - 1d..$now'

WITH sampled AS (
  SELECT
    timestamp,
    symbol,
    last((bid_price + ask_price) / 2) AS close_mid
  FROM core_price
  WHERE symbol = @symbol
    AND timestamp IN @lookback
  SAMPLE BY 1m ALIGN TO CALENDAR
)
SELECT
  timestamp,
  symbol,
  round(close_mid, 5) AS close_mid,
  LN(close_mid / LAG(close_mid)
      OVER (PARTITION BY symbol ORDER BY timestamp))
      AS log_return
FROM sampled;

How it works​

Log returns are defined as ln(P_t / P_{t-1}) where P_t is the current price and P_{t-1} is the previous price. They are preferred over simple returns because:

  • Additive over time: you can sum log returns across periods to get the total return
  • Symmetric: a round-trip always sums to zero. If the price goes from 100 to 110 and back to 100, the log returns are ln(110/100) + ln(100/110) = 0. With simple returns the same round-trip gives asymmetric percentages (+10% up, -9.09% down)

LAG(value) OVER (ORDER BY timestamp) gives the previous row's value in timestamp order. The first row returns NULL since there is no predecessor, which propagates through LN as NULL.

When computing returns at a fixed frequency, always aggregate into bars first with SAMPLE BY in a CTE, then apply LAG() to the sampled result. This ensures the window function runs over the small number of bars (e.g. 1,440 for one-minute bars over a day) rather than millions of raw ticks. See this blog post for a detailed walkthrough of why the order matters.

Related documentation