forward_returns

WITH
reverse_splits AS
(
    SELECT ticker, toDate(execution_date) AS execution_date
    FROM global_markets.stocks_splits
    WHERE split_to < split_from
      AND toDate(execution_date) >= '2025-03-01'
      AND toDate(execution_date) <  '2026-03-01'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, execution_date
),
post_split_closes AS
(
    SELECT
        d.ticker                                                                                   AS ticker,
        r.execution_date                                                                           AS execution_date,
        arrayMap(x -> tupleElement(x, 2), arraySort(groupArray((d.date, toFloat64(d.close)))))    AS closes
    FROM global_markets.stocks_daily_aggs AS d
    INNER JOIN reverse_splits AS r ON r.ticker = d.ticker
    WHERE d.ticker IN (SELECT ticker FROM reverse_splits)
      AND d.date >= '2025-03-01'
      AND d.date <  '2026-10-01'
      AND d.date >= r.execution_date
      AND d.date <  r.execution_date + 200
      AND d.close > 0
    GROUP BY d.ticker, r.execution_date
)
SELECT
    horizon,
    count()                                                                AS names_count,
    round(quantileDeterministic(0.5)(return_pct, cityHash64(ticker)), 1)   AS median_return_pct,
    round(100 * countIf(return_pct < 0) / count(), 1)                      AS share_below_split_close_pct
FROM
(
    SELECT
        ticker,
        sessions_after,
        concat(toString(sessions_after), ' sessions after')    AS horizon,
        100 * (closes[sessions_after + 1] / closes[1] - 1)     AS return_pct
    FROM post_split_closes
    ARRAY JOIN [20, 60, 120] AS sessions_after
    WHERE length(closes) > sessions_after
)
GROUP BY horizon
ORDER BY min(sessions_after)