2,173 answered market questions

WITH
    daily AS
    (
        SELECT
            ticker                                               AS symbol,
            toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
            argMax(toFloat64(close), window_start)               AS close_px
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('SPY', 'QQQ', 'AAPL', 'MSFT', 'NVDA', 'KO')
          AND window_start >= toDateTime('2024-12-24 00:00:00')
          AND window_start <  toDateTime('2026-01-01 05:00:00')
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
             + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
             + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
        GROUP BY symbol, session_date
    ),
    stepped AS
    (
        SELECT
            symbol,
            session_date,
            close_px,
            lagInFrame(close_px, 1) OVER (PARTITION BY symbol ORDER BY session_date
                                          ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_px
        FROM daily
    ),
    excess AS
    (
        SELECT
            symbol,
            close_px / prev_px - 1 - 0.0425 / 252 AS ex_ret
        FROM stepped
        WHERE prev_px > 0
          AND session_date >= toDate('2025-01-01')
    )
SELECT
    symbol,
    round(avg(ex_ret) / stddevSamp(ex_ret) * sqrt(252), 2)                     AS sharpe_ratio,
    round(avg(ex_ret) / sqrt(avg(pow(least(ex_ret, 0.0), 2))) * sqrt(252), 2)  AS sortino_ratio,
    round(countIf(ex_ret < 0) * 100.0 / count(), 1)                            AS down_day_pct
FROM excess
GROUP BY symbol
HAVING count() > 200 AND countIf(ex_ret < 0) > 0
ORDER BY sortino_ratio DESC