3,256 answered market questions

WITH sess AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           sum(toFloat64(volume)) AS vol,
           sum(toFloat64(close) * toFloat64(volume)) AS dollars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 70 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')
    GROUP BY ticker, d
),
cal AS (
    SELECT d, row_number() OVER (ORDER BY d DESC) AS rn
    FROM (SELECT DISTINCT d FROM sess)
),
per_name AS (
    SELECT s.ticker AS ticker,
           avgIf(s.vol, c.rn <= 5) AS adv_recent,
           avgIf(s.vol, c.rn BETWEEN 6 AND 45) AS adv_base,
           sumIf(s.dollars, c.rn <= 5) AS dollar_recent,
           countIf(c.rn <= 5) AS recent_sessions,
           countIf(c.rn BETWEEN 6 AND 45) AS base_sessions
    FROM sess s INNER JOIN cal c ON s.d = c.d
    GROUP BY s.ticker
    HAVING adv_base > 100000 AND dollar_recent >= 500000000 AND recent_sessions = 5 AND base_sessions >= 35
),
scored AS (
    SELECT ticker,
           multiIf(adv_recent / adv_base >= 10, 1,
                   adv_recent / adv_base >= 5, 2,
                   adv_recent / adv_base >= 3, 3,
                   adv_recent / adv_base >= 2, 4,
                   adv_recent / adv_base >= 1.5, 5,
                   adv_recent / adv_base >= 1, 6, 7) AS bucket_key
    FROM per_name
),
buckets AS (
    SELECT arrayJoin([(1, '10x or more'), (2, '5x to 10x'), (3, '3x to 5x'), (4, '2x to 3x'),
                      (5, '1.5x to 2x'), (6, '1x to 1.5x'), (7, 'below 1x')]) AS bk
)
SELECT bk.2 AS rvol_bucket,
       countIf(scored.bucket_key = bk.1) AS names,
       round(100.0 * countIf(scored.bucket_key = bk.1) / count(), 1) AS pct_of_universe,
       count() AS universe_names
FROM scored CROSS JOIN buckets
GROUP BY bk
ORDER BY bk.1 ASC