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 close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY','AAPL','MSFT','NVDA','AMZN','GOOGL','META','TSLA','JPM','XOM','JNJ','WMT','PG','KO','PEP','HD','MRK','LLY','COST','CVX','ORCL','CSCO','INTC','VZ','MCD','NKE','DIS','CAT','HON','TXN','AMD','NFLX','MO','LMT','UNH')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2021-01-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2025-12-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, dt
),
per_year AS (
    SELECT ticker, toYear(dt) AS yr,
           argMin(close_px, dt) AS first_px,
           argMax(close_px, dt) AS last_px
    FROM daily
    GROUP BY ticker, yr
),
perf AS (
    SELECT yr, ticker, (last_px / first_px - 1) * 100 AS ret_pct FROM per_year
),
bench AS (
    SELECT yr, ret_pct AS index_pct FROM perf WHERE ticker = 'SPY'
)
SELECT toString(perf.yr) AS year,
       round(any(bench.index_pct), 1) AS index_return_pct,
       round(median(perf.ret_pct), 1) AS median_stock_return_pct,
       countIf(perf.ticker != 'SPY') AS stocks_measured,
       round(100 * countIf(perf.ticker != 'SPY' AND perf.ret_pct > bench.index_pct)
             / countIf(perf.ticker != 'SPY'), 1) AS pct_beating_index
FROM perf
INNER JOIN bench ON perf.yr = bench.yr
GROUP BY perf.yr
ORDER BY perf.yr