주식 병합 일정 및 기업별 비율 확인

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

SELECT
    price_bucket,
    upcoming_count,
    round(100.0 * upcoming_count / greatest(sum(upcoming_count) OVER (), 1), 1)   AS upcoming_pct,
    past_year_count,
    round(100.0 * past_year_count / greatest(sum(past_year_count) OVER (), 1), 1) AS past_year_pct
FROM
(
    SELECT
        b.ord                                          AS ord,
        b.price_bucket                                 AS price_bucket,
        toUInt32(countIf(m.cohort = 'upcoming'))       AS upcoming_count,
        toUInt32(countIf(m.cohort = 'past_year'))      AS past_year_count
    FROM
    (
        SELECT
            arrayJoin([1, 2, 3])                                          AS ord,
            multiIf(ord = 1, 'Under $1', ord = 2, '$1 to $5', 'Over $5')  AS price_bucket
    ) AS b
    LEFT JOIN
    (
        SELECT
            cohort,
            multiIf(pre_close < 1, 1, pre_close < 5, 2, 3) AS ord
        FROM
        (
            SELECT
                'upcoming'                          AS cohort,
                s.ticker                            AS ticker,
                argMax(toFloat64(d.close), d.date)  AS pre_close
            FROM
            (
                SELECT ticker, execution_date
                FROM global_markets.stocks_splits
                WHERE execution_date >= today()
                  AND split_from > split_to
                  AND ticker NOT IN ('SPCX')
                GROUP BY ticker, execution_date
            ) AS s
            INNER JOIN
            (
                SELECT ticker, date, close
                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
                  )
            ) AS d ON d.ticker = s.ticker
            GROUP BY s.ticker, s.execution_date

            UNION ALL

            SELECT
                'past_year'                                                                  AS cohort,
                s.ticker                                                                     AS ticker,
                argMin(toFloat64(d.close), d.date) * any(s.to_shares) / any(s.from_shares)  AS pre_close
            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() - 365
                  AND execution_date <  today()
                  AND split_from > split_to
                  AND split_to > 0
                  AND ticker NOT IN ('SPCX')
                GROUP BY ticker, execution_date
            ) AS s
            INNER JOIN
            (
                SELECT ticker, date, close
                FROM global_markets.stocks_daily_aggs
                WHERE date >= today() - 365
                  AND date <  today()
                  AND ticker IN
                  (
                      SELECT ticker
                      FROM global_markets.stocks_splits
                      WHERE execution_date >= today() - 365
                        AND execution_date <  today()
                        AND split_from > split_to
                  )
            ) AS d ON d.ticker = s.ticker
            WHERE d.date >= s.execution_date
              AND d.date <  s.execution_date + 7
            GROUP BY s.ticker, s.execution_date
        )
    ) AS m ON m.ord = b.ord
    GROUP BY b.ord, b.price_bucket
)
ORDER BY ord

SELECT
    toString(month_start)                                         AS month,
    formatDateTime(month_start, '%b %Y')                          AS month_label,
    countIf(from_shares > to_shares)                              AS reverse_splits,
    countIf(to_shares > from_shares)                              AS forward_splits,
    round(100.0 * countIf(from_shares > to_shares) / count(), 1)  AS reverse_share_pct
FROM
(
    SELECT
        toStartOfMonth(execution_date)  AS month_start,
        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 >= addMonths(toStartOfMonth(today()), -12)
      AND execution_date <  toStartOfMonth(today())
      AND split_from != split_to
      AND ticker NOT IN ('SPCX')
    GROUP BY month_start, ticker, execution_date
)
GROUP BY month_start
ORDER BY month_start

SELECT
    toString(toYear(execution_date))                              AS year,
    countIf(from_shares > to_shares)                              AS reverse_splits,
    countIf(to_shares > from_shares)                              AS forward_splits,
    round(100.0 * countIf(from_shares > to_shares) / count(), 1)  AS reverse_share_pct
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 >= toDate('2019-01-01')
      AND execution_date <= today()
      AND split_from != split_to
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, execution_date
)
GROUP BY year
ORDER BY year