3,214 answered market questions

WITH
    px AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY','AAPL','MSFT','NVDA','TSLA','COIN','KO','JNJ','XOM','GLD','TLT')
          AND date >= '2025-08-22'
          AND date <= '2026-08-21'
    ),
    daily_ret AS
    (
        SELECT
            ticker,
            date,
            close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS ret
        FROM px
    ),
    idx AS
    (
        SELECT date, ret AS index_ret
        FROM daily_ret
        WHERE ticker = 'SPY' AND isFinite(ret)
    )
SELECT
    s.ticker                                                     AS symbol,
    round(avgIf(abs(s.ret) * 100, abs(i.index_ret) < 0.0025), 2) AS move_on_flat_index_pct,
    round(avg(abs(s.ret) * 100), 2)                              AS move_full_sample_pct,
    countIf(abs(i.index_ret) < 0.0025)                           AS flat_index_count
FROM daily_ret AS s
INNER JOIN idx AS i ON i.date = s.date
WHERE s.ticker != 'SPY' AND isFinite(s.ret)
GROUP BY symbol
HAVING countIf(abs(i.index_ret) < 0.0025) > 0
ORDER BY move_on_flat_index_pct DESC