pay_days

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