candidates

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