2,173 answered market questions

WITH daily AS (
    SELECT ticker,
           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 IN ('SPY', 'KO', 'JNJ', 'MSFT', 'NVDA', 'TSLA')
      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 ticker, dt
),
steps AS (
    SELECT ticker, dt, c,
           lagInFrame(c) OVER (PARTITION BY ticker ORDER BY dt
                               ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev
    FROM daily
),
rets AS (
    SELECT ticker, c / prev - 1 AS ret
    FROM steps
    WHERE prev > 0 AND c != prev
)
SELECT ticker,
       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 ticker
HAVING countIf(ret > 0) > 0 AND countIf(ret < 0) > 0
ORDER BY full_kelly_x DESC