3,256 answered market questions

SELECT
    toString(date)                                                     AS session_date,
    uniqExactIf(ticker, close < 1)                                     AS names_under_1,
    round(100 * uniqExactIf(ticker, close < 1) / uniqExact(ticker), 2) AS share_of_listed_pct
FROM global_markets.stocks_daily_aggs
WHERE date >= today() - 150
  AND 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 date
ORDER BY date