3,256 answered market questions

WITH
    divs AS
    (
        SELECT
            ex_dividend_date            AS ex_date,
            any(pay_date)               AS pay_date,
            max(toFloat64(cash_amount)) AS cash_amount
        FROM global_markets.stocks_dividends
        WHERE ticker = 'SPY'
          AND ex_dividend_date >= today() - 1095
          AND ex_dividend_date <  today()
        GROUP BY ex_dividend_date
    ),
    px AS
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            toFloat64(argMax(close, window_start))               AS close_px
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 1105
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) <  960
        GROUP BY d
    )
SELECT
    toString(divs.ex_date)                         AS ex_date,
    formatDateTime(divs.ex_date, '%b %e, %Y')      AS ex_date_label,
    formatDateTime(divs.pay_date, '%b %e, %Y')     AS pay_date_label,
    round(divs.cash_amount, 4)                     AS cash_amount,
    round(divs.cash_amount / px.close_px * 100, 3) AS pct_of_price
FROM divs
INNER JOIN px ON px.d = divs.ex_date
ORDER BY ex_date