Lag-one autocorrelation of daily returns: twelve household names, July 2021 to June 2026

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS dt,
           argMax(toFloat64(close), window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY','AAPL','MSFT','KO','JNJ','XOM','JPM','WMT','NVDA','TSLA','PG','HD')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2021-07-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, dt
),
returns AS (
    SELECT ticker, dt,
           close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY dt) - 1 AS ret
    FROM daily
),
paired AS (
    SELECT ticker, ret,
           lagInFrame(ret) OVER (PARTITION BY ticker ORDER BY dt) AS prev_ret
    FROM returns
    WHERE ret IS NOT NULL AND ret > -0.5 AND ret < 0.5
)
SELECT ticker,
       count() AS trading_days,
       round(corr(ret, prev_ret), 3) AS lag1_autocorrelation,
       round(abs(corr(ret, prev_ret)), 3) AS abs_autocorrelation
FROM paired
WHERE prev_ret IS NOT NULL
GROUP BY ticker
ORDER BY abs_autocorrelation DESC