First week post split average close, and what half a share would pay

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.

WITH recent_reverse_splits AS
(
    SELECT
        ticker,
        max(execution_date)                                           AS split_date,
        toUInt32(round(toFloat64(argMax(split_from, execution_date))
                     / toFloat64(argMax(split_to, execution_date))))  AS ratio
    FROM global_markets.stocks_splits
    WHERE split_from > split_to
      AND execution_date >= today() - 400
      AND execution_date <= today() - 15
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
)
SELECT
    r.ticker                                AS ticker,
    concat('1-for-', toString(r.ratio))     AS split_terms,
    round(avg(toFloat64(d.close)), 2)       AS avg_close_first_week,
    round(avg(toFloat64(d.close)) / 2, 2)   AS half_share_payout
FROM recent_reverse_splits AS r
INNER JOIN global_markets.stocks_daily_aggs AS d ON d.ticker = r.ticker
WHERE d.date >= r.split_date
  AND d.date <  r.split_date + 8
  AND d.date >= today() - 420
GROUP BY r.ticker, r.ratio
ORDER BY avg_close_first_week DESC
LIMIT 12