未来两周股息支付日期

SELECT
    d.ticker                                                          AS ticker,
    concat(formatDateTime(d.pay_date, '%a %b '), toString(toDayOfMonth(d.pay_date))) AS pays_on,
    toString(d.ex_dividend_date)                                      AS ex_on,
    if(ifNull(d.record_date > toDate('2000-01-01'), 0), toString(d.record_date), '') AS record_on,
    d.cash_per_share                                                  AS cash_per_share,
    dateDiff('day', d.ex_dividend_date, d.pay_date)                   AS ex_to_pay_days,
    round(toFloat64(r.latest_market_cap) / 1e9, 1)                    AS market_cap_bn,
    multiIf(d.pay_date < toMonday(today()) + 7,  'this week',
            d.pay_date < toMonday(today()) + 14, 'next week',
                                                 'week after next')   AS paid_in
FROM
(
    SELECT
        ticker,
        pay_date,
        max(ex_dividend_date)                 AS ex_dividend_date,
        max(record_date)                      AS record_date,
        round(toFloat64(max(cash_amount)), 4) AS cash_per_share
    FROM global_markets.stocks_dividends
    WHERE pay_date >= today()
      AND pay_date <  today() + 14
      AND cash_amount > 0
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, pay_date
) AS d
INNER JOIN
(
    SELECT
        ticker,
        argMax(market_cap, date) AS latest_market_cap
    FROM global_markets.stocks_ratios
    WHERE date >= today() - 200
      AND market_cap > 0
    GROUP BY ticker
) AS r ON r.ticker = d.ticker
WHERE d.ex_dividend_date < d.pay_date
ORDER BY r.latest_market_cap DESC, d.pay_date, d.ticker
LIMIT 30

SELECT
    toString(d.pay_date)                                              AS payment_date,
    concat(formatDateTime(d.pay_date, '%a %b '), toString(toDayOfMonth(d.pay_date))) AS paying_on,
    count()                                                           AS payers,
    countIf(toFloat64(r.latest_market_cap) >= 1e10)                   AS large_cap_payers
FROM
(
    SELECT
        ticker,
        pay_date,
        max(ex_dividend_date) AS ex_dividend_date
    FROM global_markets.stocks_dividends
    WHERE pay_date >= today()
      AND pay_date <  today() + 14
      AND cash_amount > 0
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, pay_date
) AS d
LEFT JOIN
(
    SELECT
        ticker,
        argMax(market_cap, date) AS latest_market_cap
    FROM global_markets.stocks_ratios
    WHERE date >= today() - 200
      AND market_cap > 0
    GROUP BY ticker
) AS r ON r.ticker = d.ticker
WHERE d.ex_dividend_date < d.pay_date
GROUP BY d.pay_date
ORDER BY d.pay_date

SELECT
    gap_bucket,
    payouts,
    round(100 * payouts / sum(payouts) OVER (), 1)                                        AS share_pct,
    round(100 * sum(payouts) OVER (ORDER BY bucket_order) / sum(payouts) OVER (), 1)      AS cumulative_pct
FROM
(
    SELECT
        multiIf(gap_days <= 7,  '0-7 days',
                gap_days <= 14, '8-14 days',
                gap_days <= 21, '15-21 days',
                gap_days <= 30, '22-30 days',
                gap_days <= 45, '31-45 days',
                                '46+ days')                  AS gap_bucket,
        multiIf(gap_days <= 7, 1, gap_days <= 14, 2, gap_days <= 21, 3,
                gap_days <= 30, 4, gap_days <= 45, 5, 6)     AS bucket_order,
        count()                                              AS payouts
    FROM
    (
        SELECT dateDiff('day', ex_dividend_date, pay_date) AS gap_days
        FROM global_markets.stocks_dividends
        WHERE ex_dividend_date >= today() - 365
          AND ex_dividend_date <  today()
          AND pay_date > ex_dividend_date
          AND cash_amount > 0
        GROUP BY ticker, ex_dividend_date, pay_date
    )
    GROUP BY gap_bucket, bucket_order
)
ORDER BY bucket_order

SELECT
    sector,
    quantileExact(0.5)(gap_days)   AS median_gap_days,
    min(gap_days)                  AS shortest_gap_days,
    max(gap_days)                  AS longest_gap_days,
    count()                        AS payouts_counted
FROM
(
    SELECT
        multiIf(
            ticker IN ('O', 'PLD', 'AMT', 'SPG', 'WELL', 'PSA', 'EQIX', 'DLR', 'VICI', 'CCI', 'AVB', 'EQR'),      'REITs',
            ticker IN ('DUK', 'SO', 'NEE', 'D', 'AEP', 'XEL', 'ED', 'EXC', 'SRE', 'PEG', 'WEC', 'ETR'),          'Utilities',
            ticker IN ('AAPL', 'MSFT', 'AVGO', 'CSCO', 'ORCL', 'TXN', 'QCOM', 'IBM', 'ADP', 'ACN', 'ADI', 'INTU'), 'Tech',
            ticker IN ('JPM', 'BAC', 'WFC', 'C', 'GS', 'MS', 'USB', 'PNC', 'TFC', 'BLK', 'AXP', 'SCHW'),          'Banks and financials',
            ticker IN ('KO', 'PEP', 'PG', 'WMT', 'COST', 'MDLZ', 'CL', 'KMB', 'PM', 'MO', 'GIS', 'HSY'),          'Consumer staples',
            ticker IN ('JNJ', 'PFE', 'MRK', 'ABBV', 'ABT', 'LLY', 'AMGN', 'BMY', 'MDT', 'GILD', 'CVS', 'UNH'),    'Healthcare',
            ticker IN ('XOM', 'CVX', 'COP', 'EOG', 'SLB', 'KMI', 'WMB', 'OKE', 'PSX', 'VLO', 'MPC', 'DVN'),       'Energy',
            'other')                                         AS sector,
        dateDiff('day', ex_dividend_date, pay_date)          AS gap_days
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= today() - 730
      AND ex_dividend_date <  today()
      AND pay_date > ex_dividend_date
      AND cash_amount > 0
    GROUP BY ticker, ex_dividend_date, pay_date
)
WHERE sector != 'other'
GROUP BY sector
ORDER BY indexOf(['REITs', 'Utilities', 'Tech', 'Banks and financials', 'Consumer staples', 'Healthcare', 'Energy'], sector)