2,358 answered market questions

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