aapl_dividends

SELECT
    toString(d.ex_dt)                                    AS ex_date,
    formatDateTime(d.ex_dt, '%W')                        AS weekday,
    formatDateTime(d.decl_dt, '%b %e, %Y')               AS declared_label,
    formatDateTime(d.ex_dt, '%b %e, %Y')                 AS ex_label,
    formatDateTime(d.rec_dt, '%a, %b %e')                AS record_label,
    formatDateTime(d.pay_dt, '%b %e')                    AS pay_label,
    d.cash                                               AS cash_amount,
    dateDiff('day', d.ex_dt, d.rec_dt)                   AS days_ex_to_record,
    formatDateTime(px.prior_sess, '%a, %b %e')           AS last_session_with_dividend,
    formatDateTime(px.next_sess, '%a, %b %e')            AS buy_on_ex_settles,
    dateDiff('day', d.rec_dt, px.next_sess)              AS settles_days_after_record,
    dateDiff('day', d.rec_dt, d.pay_dt)                  AS days_record_to_pay
FROM
(
    SELECT
        any(ex_dividend_date)                 AS ex_dt,
        any(declaration_date)                 AS decl_dt,
        any(record_date)                      AS rec_dt,
        any(pay_date)                         AS pay_dt,
        round(toFloat64(any(cash_amount)), 4) AS cash
    FROM global_markets.stocks_dividends
    WHERE ticker = 'AAPL'
      AND ex_dividend_date >= '2024-06-01'
      AND ex_dividend_date <  today() - 3
    GROUP BY id
) AS d
INNER JOIN
(
    SELECT
        sess,
        lagInFrame(sess, 1)  OVER (ORDER BY sess ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prior_sess,
        leadInFrame(sess, 1) OVER (ORDER BY sess ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next_sess
    FROM
    (
        SELECT date AS sess
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'AAPL'
          AND date >= '2024-05-01'
          AND date <  today()
        GROUP BY date
    )
) AS px ON px.sess = d.ex_dt
WHERE px.next_sess > px.sess
  AND px.prior_sess >= toDate('2024-05-01')
ORDER BY d.ex_dt DESC
LIMIT 8