pay_calendar

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