Stock Split Candidates: Who Might Split Next?

WITH
latest_px AS
(
    SELECT
        ticker,
        argMax(close, date) AS last_close,
        max(date)           AS px_date
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 10
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    HAVING last_close >= 500
       AND last_close <  50000
),
big_caps AS
(
    SELECT
        ticker,
        argMax(market_cap, date) AS market_cap
    FROM global_markets.stocks_ratios
    WHERE date >= today() - 45
    GROUP BY ticker
    HAVING toFloat64(market_cap) >= 20e9
),
announced AS
(
    SELECT DISTINCT ticker
    FROM global_markets.stocks_splits
    WHERE execution_date > today()
),
cand AS
(
    SELECT
        p.ticker     AS ticker,
        p.last_close AS last_close,
        p.px_date    AS px_date,
        m.market_cap AS market_cap
    FROM latest_px AS p
    INNER JOIN big_caps AS m ON m.ticker = p.ticker
    WHERE p.ticker NOT IN (SELECT ticker FROM announced)
),
last_split AS
(
    SELECT
        ticker,
        max(execution_date)                                                                                   AS last_split_date,
        argMax(concat(toString(toFloat64(split_to)), '-for-', toString(toFloat64(split_from))), execution_date) AS last_split_ratio,
        uniqExact(execution_date)                                                                             AS forward_splits
    FROM global_markets.stocks_splits
    WHERE split_to > split_from
      AND execution_date <= today()
      AND ticker IN (SELECT ticker FROM cand)
    GROUP BY ticker
),
pre_split AS
(
    SELECT
        d.ticker                AS ticker,
        argMax(d.close, d.date) AS pre_split_close
    FROM
    (
        SELECT ticker, date, close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN (SELECT ticker FROM last_split)
    ) AS d
    INNER JOIN last_split AS s ON s.ticker = d.ticker
    WHERE d.date <  s.last_split_date
      AND d.date >= s.last_split_date - 7
    GROUP BY d.ticker
),
to_raw AS
(
    SELECT
        x.ticker                                                                    AS ticker,
        arrayProduct(groupArray(toFloat64(x.to_shares) / toFloat64(x.from_shares))) AS factor
    FROM
    (
        SELECT
            ticker,
            execution_date,
            any(split_from) AS from_shares,
            any(split_to)   AS to_shares
        FROM global_markets.stocks_splits
        WHERE execution_date <= today()
          AND ticker IN (SELECT ticker FROM last_split)
        GROUP BY ticker, execution_date
    ) AS x
    INNER JOIN last_split AS ls ON ls.ticker = x.ticker
    WHERE x.execution_date >= ls.last_split_date
    GROUP BY x.ticker
)
SELECT
    c.ticker                                                                          AS ticker,
    round(toFloat64(c.last_close), 2)                                                 AS share_price,
    if(ps.pre_split_close > 0, round(toFloat64(ps.pre_split_close) * f.factor, 2), 0) AS price_before_last_split,
    concat('$', toString(toUInt64(round(toFloat64(c.market_cap) / 1e9))), 'B')        AS market_value,
    toString(ifNull(s.forward_splits, 0))                                             AS forward_splits_on_record,
    if(s.last_split_date > toDate('1971-01-01'), toString(s.last_split_date), 'none') AS last_forward_split,
    if(s.last_split_ratio != '', s.last_split_ratio, 'none')                          AS last_split_ratio,
    concat(monthName(c.px_date), ' ', toString(toDayOfMonth(c.px_date)), ', ', toString(toYear(c.px_date))) AS as_of
FROM cand AS c
LEFT JOIN last_split AS s  ON s.ticker = c.ticker
LEFT JOIN pre_split  AS ps ON ps.ticker = c.ticker
LEFT JOIN to_raw     AS f  ON f.ticker = c.ticker
ORDER BY c.last_close DESC
LIMIT 15

WITH
big_caps AS
(
    SELECT
        ticker,
        argMax(market_cap, date) AS market_cap
    FROM global_markets.stocks_ratios
    WHERE date >= today() - 45
    GROUP BY ticker
    HAVING toFloat64(market_cap) >= 20e9
),
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 execution_date <= today()
      AND ticker NOT IN ('SPCX')
      AND ticker IN (SELECT ticker FROM big_caps)
    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.execution_date >= '2020-01-01'
      AND a.to_shares > a.from_shares
    GROUP BY a.ticker, a.execution_date, a.from_shares, a.to_shares
)
SELECT
    s.ticker                                                                            AS ticker,
    toString(s.execution_date)                                                          AS executed,
    concat(monthName(s.execution_date), ' ', toString(toDayOfMonth(s.execution_date)), ', ', toString(toYear(s.execution_date))) AS executed_on,
    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
(
    SELECT ticker, date, close
    FROM global_markets.stocks_daily_aggs
    WHERE date >= '2019-12-01'
      AND ticker IN (SELECT ticker FROM splits)
) AS d
INNER JOIN splits AS s ON s.ticker = d.ticker
WHERE d.date <  s.execution_date
  AND d.date >= s.execution_date - 7
GROUP BY s.ticker, s.execution_date, s.to_shares, s.from_shares, s.to_raw
HAVING pre_split_close >= 300
ORDER BY pre_split_close DESC
LIMIT 16

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

WITH
on_file AS
(
    SELECT
        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 execution_date
),
restated AS
(
    SELECT
        d.date AS date,
        any(toFloat64(d.close)) * arrayProduct(groupArray(if(s.execution_date > d.date, toFloat64(s.to_shares) / toFloat64(s.from_shares), 1.0))) AS raw_close
    FROM
    (
        SELECT date, close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'AAPL'
          AND date >= '2014-01-01'
          AND date <  '2021-01-01'
    ) AS d
    CROSS JOIN on_file AS s
    GROUP BY d.date
)
SELECT
    toString(toStartOfMonth(date))                AS month,
    formatDateTime(toStartOfMonth(date), '%b %Y') AS period_label,
    round(max(raw_close), 2)                      AS highest_close,
    round(min(raw_close), 2)                      AS lowest_close
FROM restated
GROUP BY toStartOfMonth(date)
ORDER BY toStartOfMonth(date)

WITH
big_caps AS
(
    SELECT
        ticker,
        argMax(market_cap, date) AS market_cap
    FROM global_markets.stocks_ratios
    WHERE date >= today() - 45
    GROUP BY ticker
    HAVING toFloat64(market_cap) >= 20e9
),
ever_split AS
(
    SELECT DISTINCT ticker
    FROM global_markets.stocks_splits
    WHERE split_to > split_from
)
SELECT
    d.ticker                                                            AS ticker,
    uniqExactIf(d.date, d.close >= 500)                                 AS closes_above_500,
    concat(monthName(minIf(d.date, d.close >= 500)), ' ', toString(toDayOfMonth(minIf(d.date, d.close >= 500))), ', ', toString(toYear(minIf(d.date, d.close >= 500)))) AS first_above_500_in_window,
    concat('$', toString(round(toFloat64(argMax(d.close, d.date)), 2))) AS latest_close,
    concat('$', toString(round(toFloat64(max(d.close)), 2)))            AS highest_close
FROM global_markets.stocks_daily_aggs AS d
WHERE d.date >= today() - 1826
  AND d.ticker IN (SELECT ticker FROM big_caps)
  AND d.ticker NOT IN (SELECT ticker FROM ever_split)
  AND d.ticker NOT IN ('SPCX')
GROUP BY d.ticker
HAVING argMax(d.close, d.date) >= 500
   AND closes_above_500 >= 250
ORDER BY closes_above_500 DESC
LIMIT 12