Where reverse-split companies stood a year later (splits executed 12-18 months ago)

WITH cohort AS (
    SELECT ticker, min(execution_date) AS ex
    FROM global_markets.stocks_splits
    WHERE adjustment_type = 'reverse_split'
      AND execution_date >= today() - INTERVAL 540 DAY
      AND execution_date <= today() - INTERVAL 365 DAY
      AND ticker != 'SPCX'
    GROUP BY ticker
),
repeats AS (
    SELECT c.ticker AS ticker, count() AS later_reverse_splits
    FROM cohort AS c
    INNER JOIN global_markets.stocks_splits AS s ON s.ticker = c.ticker
    WHERE s.adjustment_type = 'reverse_split'
      AND s.execution_date > c.ex
      AND s.execution_date <= c.ex + INTERVAL 365 DAY
    GROUP BY c.ticker
),
bars AS (
    SELECT ticker, toDate(toTimeZone(window_start, 'America/New_York')) AS d
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM cohort)
      AND (
            (window_start >= today() - INTERVAL 545 DAY AND window_start < today() - INTERVAL 360 DAY)
            OR window_start >= today() - INTERVAL 45 DAY
          )
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, d
),
status AS (
    SELECT c.ticker AS ticker,
           countIf(b.d < c.ex) AS pre_split_days,
           countIf(b.d >= today() - 45) AS recent_days,
           any(ifNull(r.later_reverse_splits, 0)) AS later_reverse
    FROM cohort AS c
    INNER JOIN bars AS b ON b.ticker = c.ticker
    LEFT JOIN repeats AS r ON r.ticker = c.ticker
    GROUP BY c.ticker
    HAVING pre_split_days > 0
)
SELECT multiIf(recent_days = 0, 'Stopped printing trades',
               later_reverse > 0, 'Still trading, split again in reverse',
               'Still trading, no second reverse split') AS outcome_one_year_on,
       count() AS companies,
       round(100.0 * count() / sum(count()) OVER (), 1) AS pct_of_cohort
FROM status
GROUP BY outcome_one_year_on
ORDER BY indexOf(['Still trading, no second reverse split',
                  'Still trading, split again in reverse',
                  'Stopped printing trades'], outcome_one_year_on)