Pre-split price of reverse splits: the calendar ahead versus the trailing twelve months

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

SELECT
    price_bucket,
    upcoming_count,
    round(100.0 * upcoming_count / greatest(sum(upcoming_count) OVER (), 1), 1)   AS upcoming_pct,
    past_year_count,
    round(100.0 * past_year_count / greatest(sum(past_year_count) OVER (), 1), 1) AS past_year_pct
FROM
(
    SELECT
        b.ord                                          AS ord,
        b.price_bucket                                 AS price_bucket,
        toUInt32(countIf(m.cohort = 'upcoming'))       AS upcoming_count,
        toUInt32(countIf(m.cohort = 'past_year'))      AS past_year_count
    FROM
    (
        SELECT
            arrayJoin([1, 2, 3])                                          AS ord,
            multiIf(ord = 1, 'Under $1', ord = 2, '$1 to $5', 'Over $5')  AS price_bucket
    ) AS b
    LEFT JOIN
    (
        SELECT
            cohort,
            multiIf(pre_close < 1, 1, pre_close < 5, 2, 3) AS ord
        FROM
        (
            SELECT
                'upcoming'                          AS cohort,
                s.ticker                            AS ticker,
                argMax(toFloat64(d.close), d.date)  AS pre_close
            FROM
            (
                SELECT ticker, execution_date
                FROM global_markets.stocks_splits
                WHERE execution_date >= today()
                  AND split_from > split_to
                  AND ticker NOT IN ('SPCX')
                GROUP BY ticker, execution_date
            ) AS s
            INNER JOIN
            (
                SELECT ticker, date, close
                FROM global_markets.stocks_daily_aggs
                WHERE date >= today() - 30
                  AND date <  today()
                  AND ticker IN
                  (
                      SELECT ticker
                      FROM global_markets.stocks_splits
                      WHERE execution_date >= today()
                        AND split_from > split_to
                  )
            ) AS d ON d.ticker = s.ticker
            GROUP BY s.ticker, s.execution_date

            UNION ALL

            SELECT
                'past_year'                                                                  AS cohort,
                s.ticker                                                                     AS ticker,
                argMin(toFloat64(d.close), d.date) * any(s.to_shares) / any(s.from_shares)  AS pre_close
            FROM
            (
                SELECT
                    ticker,
                    execution_date,
                    max(toFloat64(split_from)) AS from_shares,
                    max(toFloat64(split_to))   AS to_shares
                FROM global_markets.stocks_splits
                WHERE execution_date >= today() - 365
                  AND execution_date <  today()
                  AND split_from > split_to
                  AND split_to > 0
                  AND ticker NOT IN ('SPCX')
                GROUP BY ticker, execution_date
            ) AS s
            INNER JOIN
            (
                SELECT ticker, date, close
                FROM global_markets.stocks_daily_aggs
                WHERE date >= today() - 365
                  AND date <  today()
                  AND ticker IN
                  (
                      SELECT ticker
                      FROM global_markets.stocks_splits
                      WHERE execution_date >= today() - 365
                        AND execution_date <  today()
                        AND split_from > split_to
                  )
            ) AS d ON d.ticker = s.ticker
            WHERE d.date >= s.execution_date
              AND d.date <  s.execution_date + 7
            GROUP BY s.ticker, s.execution_date
        )
    ) AS m ON m.ord = b.ord
    GROUP BY b.ord, b.price_bucket
)
ORDER BY ord