The board leader, day by day: daily relative volume and open-to-close change (last 15 sessions)

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 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,
           argMin(toFloat64(open), toTimeZone(window_start, 'America/New_York')) AS day_open,
           argMax(toFloat64(close), toTimeZone(window_start, 'America/New_York')) AS day_close
    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
),
leader AS (
    SELECT ticker, adv_base
    FROM per_name
    ORDER BY adv_recent / adv_base DESC, ticker ASC
    LIMIT 1
),
path AS (
    SELECT formatDateTime(s.d, '%Y-%m-%d') AS session_date,
           formatDateTime(s.d, '%b %e') AS session_label,
           s.vol / l.adv_base AS rvol_day,
           100.0 * (s.day_close / s.day_open - 1) AS day_pct
    FROM sess s
    INNER JOIN cal c ON s.d = c.d
    INNER JOIN leader l ON s.ticker = l.ticker
    WHERE c.rn <= 15
)
SELECT session_date,
       session_label,
       round(rvol_day, 1) AS rvol_day,
       round(day_pct, 1) AS day_pct,
       round(max(rvol_day) OVER (), 1) AS peak_rvol_day
FROM path
ORDER BY session_date ASC