2,178 answered market questions

WITH d AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS dt,
           argMax(toFloat64(close), toTimeZone(window_start, 'America/New_York')) AS c
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY','KO','JNJ','PG','PEP','WMT','MCD','MO','VZ','XOM','JPM','HD','COST','UNH','MRK','LLY','HON','LMT','CAT','TXN','ORCL','NVDA','TSLA','AMD','NFLX')
      AND window_start >= now() - INTERVAL 800 DAY
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, dt
),
r AS (
    SELECT ticker, dt, c,
           c / lagInFrame(c) OVER (PARTITION BY ticker ORDER BY dt) - 1 AS ret
    FROM d
)
SELECT ticker,
       round(stddevSamp(ret) * sqrt(252) * 100, 1) AS annual_vol_pct,
       round((exp(sum(log(1 + ret))) - 1) * 100, 1) AS total_return_pct
FROM r
WHERE ret IS NOT NULL AND ret > -0.5 AND ret < 0.5
GROUP BY ticker
ORDER BY annual_vol_pct