price_buckets

SELECT
    bucket_label,
    countIf(px >= lo AND px < hi)                             AS names,
    round(100 * countIf(px >= lo AND px < hi) / count(), 1)   AS share_pct,
    any(session_label)                                        AS as_of
FROM
(
    SELECT
        ticker,
        argMax(toFloat64(close), _ingest_time)       AS px,
        any(formatDateTime(date, '%b %e, %Y'))       AS session_label
    FROM global_markets.stocks_daily_aggs
    WHERE date = (SELECT max(date) FROM global_markets.stocks_daily_aggs
                  WHERE ticker = 'SPY' AND date >= today() - 14)
      AND ifNull(otc, 0) = 0
      AND length(ticker) <= 4
      AND ticker NOT IN ('SPCX')
      AND volume > 0
      AND close > 0
    GROUP BY ticker
    HAVING px < 1
) AS latest
ARRAY JOIN
    [0.00, 0.10, 0.25, 0.50, 0.75]                                                             AS lo,
    [0.10, 0.25, 0.50, 0.75, 1.00]                                                             AS hi,
    ['Under $0.10', '$0.10 to $0.25', '$0.25 to $0.50', '$0.50 to $0.75', '$0.75 to $1.00']   AS bucket_label
GROUP BY lo, hi, bucket_label
ORDER BY lo