2,272 answered market questions

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS dt,
           argMax(toFloat64(close), window_start) AS c
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2016-01-01 00:00:00'
      AND window_start <  '2026-01-01 00:00:00'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY dt
),
steps AS (
    SELECT dt, c,
           lagInFrame(c) OVER (ORDER BY dt ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev
    FROM daily
),
rets AS (
    SELECT dt, c / prev - 1 AS ret
    FROM steps
    WHERE prev > 0 AND c != prev
)
SELECT toString(toYear(dt)) AS year,
       round(100 * countIf(ret > 0) / count(), 1) AS win_rate_pct,
       round(100 * avgIf(ret, ret > 0), 2) AS avg_gain_pct,
       round(100 * abs(avgIf(ret, ret < 0)), 2) AS avg_loss_pct,
       round(countIf(ret > 0) / count() / abs(avgIf(ret, ret < 0))
             - countIf(ret < 0) / count() / avgIf(ret, ret > 0), 1) AS full_kelly_x
FROM rets
GROUP BY year
HAVING countIf(ret > 0) > 0 AND countIf(ret < 0) > 0
ORDER BY year