3,256 answered market questions

SELECT
    formatDateTime(m, '%Y-%m')  AS month,
    countIf(month_close < 5)    AS under_5_count,
    countIf(month_close < 1)    AS under_1_count
FROM
(
    SELECT
        toStartOfMonth(date) AS m,
        ticker,
        argMax(close, date)  AS month_close
    FROM global_markets.stocks_daily_aggs
    WHERE date >= toStartOfMonth(today() - 730)
      AND date <  toStartOfMonth(today())
      AND volume > 0
      AND close > 0
    GROUP BY m, ticker
)
GROUP BY m
ORDER BY m