2,178 answered market questions

WITH complete AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-01-01 00: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 d
    HAVING count() >= 380
),
universe AS (
    SELECT ticker,
           sum(toFloat64(close) * toFloat64(volume)) / uniqExact(toDate(toTimeZone(window_start, 'America/New_York'))) AS adv
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 21 DAY
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= today() - 20
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM complete)
      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 ticker
    HAVING adv >= 100000000
),
edges AS (
    SELECT ticker,
        argMinIf(toFloat64(open), toTimeZone(window_start, 'America/New_York'), toDate(toTimeZone(window_start, 'America/New_York')) = (SELECT min(d) FROM complete)) AS year_open,
        argMaxIf(toFloat64(close), toTimeZone(window_start, 'America/New_York'), toDate(toTimeZone(window_start, 'America/New_York')) = (SELECT max(d) FROM complete)) AS latest_close,
        countIf(toDate(toTimeZone(window_start, 'America/New_York')) = (SELECT min(d) FROM complete)) AS bars_open,
        countIf(toDate(toTimeZone(window_start, 'America/New_York')) = (SELECT max(d) FROM complete)) AS bars_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ((window_start >= toDateTime('2026-01-01 00:00:00') AND window_start < toDateTime('2026-01-10 00:00:00'))
        OR (window_start >= now() - INTERVAL 8 DAY))
      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
      AND ticker NOT IN ('SPCX')
      AND ticker NOT IN ('KORU','SOXL','SOXS','SOXY','TQQQ','SQQQ','QQQU','SPXL','SPXS','UPRO','SPXU','SPYU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','GDXU','GDXD','FNGU','FNGD','DUST','JNUG','JDST','NUGT','BITX','BITU','SBIT','ETHU','ETHT','NVDL','NVDS','NVD','NVDX','NVDU','NVDD','NVDQ','TSLL','TSLQ','TSLZ','TSLR','TSLT','TSLS','TSDD','AAPU','AAPD','MSFU','MSFD','GGLL','GGLS','AMZU','AMZD','METU','METD','PLTU','PLTD','SMCX','SMCZ','CONL','CONI','MSTX','MSTU','MSTZ','BRKU','AMDL','AMUU','AMDD','ELIL','ELIS','HOOX','AVGX','AVGU','TSMX','TSMZ','MULL')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN toDate('2026-01-01') AND today())
    GROUP BY ticker
    HAVING bars_open >= 100 AND bars_close >= 100
)
SELECT e.ticker AS ticker,
    round((e.latest_close / e.year_open - 1) * 100, 1) AS ytd_return_pct,
    round(u.adv / 1e6, 0) AS avg_daily_dollar_m
FROM edges AS e
INNER JOIN universe AS u ON e.ticker = u.ticker
WHERE e.year_open >= 10
ORDER BY (e.latest_close / e.year_open) ASC
LIMIT 10