ex_open_basket

SELECT
    arrayJoin([toString(d.sym), 'All 20 combined'])                                  AS label,
    count()                                                                          AS ex_date_count,
    round(avg(100 * toFloat64(d.cash) / toFloat64(px.prior_close)), 2)               AS avg_dividend_pct_of_price,
    round(avg(100 * (toFloat64(px.ex_open) / toFloat64(px.prior_close) - 1)), 2)     AS avg_close_to_open_pct
FROM
(
    SELECT
        any(ticker)           AS sym,
        any(ex_dividend_date) AS ex_dt,
        any(cash_amount)      AS cash
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('AAPL', 'MSFT', 'KO', 'JNJ', 'PG', 'PEP', 'JPM', 'XOM', 'CVX', 'HD',
                     'MCD', 'WMT', 'IBM', 'CSCO', 'VZ', 'MRK', 'PFE', 'ABBV', 'TXN', 'LLY')
      AND ex_dividend_date >= '2024-06-01'
      AND ex_dividend_date <  today() - 3
    GROUP BY id
) AS d
INNER JOIN
(
    SELECT
        sym,
        sess,
        ex_open,
        lagInFrame(close_px, 1) OVER (PARTITION BY sym ORDER BY sess ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prior_close
    FROM
    (
        SELECT
            ticker     AS sym,
            date       AS sess,
            any(open)  AS ex_open,
            any(close) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL', 'MSFT', 'KO', 'JNJ', 'PG', 'PEP', 'JPM', 'XOM', 'CVX', 'HD',
                         'MCD', 'WMT', 'IBM', 'CSCO', 'VZ', 'MRK', 'PFE', 'ABBV', 'TXN', 'LLY')
          AND date >= '2024-05-01'
          AND date <  today()
        GROUP BY sym, sess
    )
) AS px ON px.sym = d.sym AND px.sess = d.ex_dt
WHERE px.prior_close > 0
  AND px.ex_open > 0
GROUP BY label
ORDER BY (label = 'All 20 combined') DESC, avg_dividend_pct_of_price DESC