upcoming_reverse

SELECT
    s.ticker                                                               AS ticker,
    toString(s.execution_date)                                             AS effective,
    concat(formatDateTime(s.execution_date, '%b'), ' ',
           toString(toDayOfMonth(s.execution_date)))                       AS effective_label,
    concat('1-for-', toString(round(s.from_shares / s.to_shares, 2)))      AS split_ratio,
    if(c.close_as_of = toDate('1970-01-01'), NULL, round(c.last_close, 4)) AS last_close,
    if(c.close_as_of = toDate('1970-01-01'), '', toString(c.close_as_of))  AS close_as_of
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()
      AND split_from > split_to
      AND split_to > 0
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, execution_date
) AS s
LEFT JOIN
(
    SELECT
        ticker,
        argMax(toFloat64(close), date) AS last_close,
        max(date)                      AS close_as_of
    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
      )
    GROUP BY ticker
) AS c ON c.ticker = s.ticker
ORDER BY s.execution_date, s.ticker
LIMIT 80