2,170 answered market questions

WITH yearly_px AS
(
    SELECT
        toYear(toTimeZone(window_start, 'America/New_York')) AS yr,
        round(avg(toFloat64(close)), 2)                      AS avg_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'QYLD'
      AND window_start >= toDateTime('2021-01-01 05:00:00')
      AND window_start <  toDateTime('2026-01-01 05:00:00')
      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 yr
),
yearly_div AS
(
    SELECT
        toYear(ex_dividend_date)         AS yr,
        round(sum(cash_amount), 3)       AS distributions_per_share,
        count()                          AS payments
    FROM
    (
        SELECT
            ex_dividend_date,
            toFloat64(max(cash_amount)) AS cash_amount
        FROM global_markets.stocks_dividends
        WHERE ticker = 'QYLD'
          AND ex_dividend_date >= toDate('2021-01-01')
          AND ex_dividend_date <  toDate('2026-01-01')
        GROUP BY ex_dividend_date
    )
    GROUP BY yr
)
SELECT
    toString(p.yr)                                            AS year,
    d.distributions_per_share                                 AS distributions_per_share,
    p.avg_close                                               AS avg_close,
    round(100 * d.distributions_per_share / p.avg_close, 1)   AS distribution_rate_pct,
    d.payments                                                AS payments
FROM yearly_px AS p
INNER JOIN yearly_div AS d ON d.yr = p.yr
ORDER BY p.yr