Biggest stock gainers this month (companies trading $1B+ over the window)

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH window_days AS (
    SELECT d
    FROM (
        SELECT d, max(d) OVER () AS last_full
        FROM (
            SELECT d
            FROM (
                SELECT d, bars, medianExact(bars) OVER () AS typical_bars
                FROM (
                    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d, count() AS bars
                    FROM global_markets.delayed_stocks_minute_aggs
                    WHERE window_start >= now() - INTERVAL 34 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
                    GROUP BY d
                    HAVING d < toDate(toTimeZone(now(), 'America/New_York'))
                )
            )
            WHERE bars >= 0.85 * typical_bars
        )
    )
    WHERE d > last_full - 30
)
SELECT ticker,
       round((argMax(c, d) / argMin(o, d) - 1) * 100, 1) AS month_return_pct,
       round(sum(dollars) / 1e9, 1) AS month_dollar_bn
FROM (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMin(toFloat64(open), window_start) AS o,
           argMax(toFloat64(close), window_start) AS c,
           sum(toFloat64(close) * toFloat64(volume)) AS dollars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 34 DAY
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM window_days)
      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','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT')
      AND ticker IN (SELECT ticker FROM global_markets.stocks_ratios
                     WHERE market_cap > 0)
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 45 AND today())
    GROUP BY ticker, d
)
GROUP BY ticker
HAVING sum(dollars) >= 1000000000 AND argMin(o, d) >= 10 AND count() >= 15
ORDER BY argMax(c, d) / argMin(o, d) DESC
LIMIT 10