AAPL's last eight ex-dividend mornings: adjusted reference vs. the actual open

WITH divs AS (
    SELECT 1 AS k, ex_dividend_date AS ex_date, cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'AAPL' AND distribution_type = 'recurring' AND ex_dividend_date <= today()
    ORDER BY ex_dividend_date DESC
    LIMIT 8
),
rth AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           toTimeZone(window_start, 'America/New_York') AS et_ts,
           open, close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'AAPL'
      AND window_start >= now() - INTERVAL 800 DAY
      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
),
opens AS (SELECT 1 AS k, et_date, argMin(open, et_ts) AS day_open FROM rth GROUP BY et_date),
closes AS (SELECT 1 AS k, et_date, argMax(close, et_ts) AS day_close FROM rth GROUP BY et_date)
SELECT d.ex_date,
       c.day_close AS prior_session_close,
       round(c.day_close - d.cash_amount, 2) AS adjusted_reference,
       o.day_open AS ex_date_open,
       d.cash_amount AS dividend,
       round(o.day_open - c.day_close, 2) AS actual_overnight_change
FROM divs d
INNER JOIN opens o ON o.et_date = d.ex_date
ASOF INNER JOIN closes c ON c.k = d.k AND c.et_date < d.ex_date
ORDER BY d.ex_date DESC