streaks

SELECT
    threshold_label,
    countIf(streak >= threshold)                             AS names,
    round(100 * countIf(streak >= threshold) / count(), 1)   AS share_pct
FROM
(
    SELECT
        ticker,
        arrayReverseSort(groupArray((date, px)))                                                    AS closes_desc,
        arrayFirstIndex(x -> tupleElement(x, 2) >= 1, closes_desc)                                  AS first_at_or_above_1,
        if(first_at_or_above_1 = 0, toInt32(length(closes_desc)), toInt32(first_at_or_above_1) - 1) AS streak
    FROM
    (
        SELECT
            ticker,
            date,
            argMax(toFloat64(close), _ingest_time) AS px
        FROM global_markets.stocks_daily_aggs
        WHERE date >= today() - 400
          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
          AND ticker IN
          (
              SELECT ticker
              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 volume > 0
                AND close > 0
                AND close < 1
          )
        GROUP BY ticker, date
    ) AS daily
    GROUP BY ticker
) AS per_ticker
ARRAY JOIN
    [1, 30, 90, 180]                                             AS threshold,
    ['1 or more', '30 or more', '90 or more', '180 or more']     AS threshold_label
GROUP BY threshold, threshold_label
ORDER BY threshold