2,358 answered market questions

WITH
on_file AS
(
    SELECT
        ticker,
        execution_date,
        any(split_from) AS from_shares,
        any(split_to)   AS to_shares
    FROM global_markets.stocks_splits
    WHERE ticker = 'AAPL'
      AND execution_date <= today()
    GROUP BY ticker, execution_date
),
splits AS
(
    SELECT
        a.ticker         AS ticker,
        a.execution_date AS execution_date,
        a.from_shares    AS from_shares,
        a.to_shares      AS to_shares,
        arrayProduct(groupArray(toFloat64(b.to_shares) / toFloat64(b.from_shares))) AS to_raw
    FROM on_file AS a
    INNER JOIN on_file AS b ON b.ticker = a.ticker
    WHERE b.execution_date >= a.execution_date
      AND a.to_shares > a.from_shares
    GROUP BY a.ticker, a.execution_date, a.from_shares, a.to_shares
)
SELECT
    concat(monthName(s.execution_date), ' ', toString(toDayOfMonth(s.execution_date)), ', ', toString(toYear(s.execution_date))) AS label,
    toString(s.execution_date)                                                          AS executed,
    concat(toString(toFloat64(s.to_shares)), '-for-', toString(toFloat64(s.from_shares))) AS ratio,
    round(toFloat64(argMax(d.close, d.date)) * s.to_raw, 2)                             AS pre_split_close,
    round(toFloat64(argMax(d.close, d.date)) * s.to_raw * toFloat64(s.from_shares) / toFloat64(s.to_shares), 2) AS implied_post_split_price
FROM splits AS s
INNER JOIN
(
    SELECT ticker, date, close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
) AS d ON d.ticker = s.ticker
WHERE d.date <  s.execution_date
  AND d.date >= s.execution_date - 7
GROUP BY s.execution_date, s.to_shares, s.from_shares, s.to_raw
ORDER BY s.execution_date