adv_tiers

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
dedup AS
(
    SELECT
        ticker,
        date,
        toFloat64(max(volume)) AS vol
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 400
      AND ifNull(otc, 0) = 0
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, date
),
rv AS
(
    SELECT
        date,
        vol,
        avg(vol) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS avg20
    FROM dedup
),
tiered AS
(
    SELECT
        multiIf(avg20 < 100000,    '1. under 100k shares',
                avg20 < 1000000,   '2. 100k to 1m',
                avg20 < 10000000,  '3. 1m to 10m',
                                   '4. over 10m shares') AS adv_tier,
        vol / avg20                                      AS rvol
    FROM rv
    WHERE date >= today() - 370
      AND avg20 >= 1000
)
SELECT
    adv_tier,
    round(100 * countIf(rvol >= 1.5) / count(), 2) AS above_1_5x_pct,
    round(100 * countIf(rvol >= 2.0) / count(), 2) AS above_2x_pct,
    round(100 * countIf(rvol >= 5.0) / count(), 2) AS above_5x_pct
FROM tiered
GROUP BY adv_tier
ORDER BY adv_tier