3,256 answered market questions

WITH
    px AS
    (
        SELECT
            ticker,
            argMax(toFloat64(close), date) AS last_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY', 'SCHD', 'VYM', 'QQQ', 'TLT', 'JEPI')
          AND date >= today() - 30
        GROUP BY ticker
    ),
    paid AS
    (
        SELECT
            fund_ticker       AS ticker,
            sum(cash)         AS ttm_cash,
            argMax(cash, exd) AS latest_cash,
            argMax(freq, exd) AS pays_per_year
        FROM
        (
            SELECT
                any(ticker)                 AS fund_ticker,
                any(toFloat64(cash_amount)) AS cash,
                any(ex_dividend_date)       AS exd,
                any(toUInt16(frequency))    AS freq
            FROM global_markets.stocks_dividends
            WHERE ticker IN ('SPY', 'SCHD', 'VYM', 'QQQ', 'TLT', 'JEPI')
              AND ex_dividend_date > today() - 365
              AND ex_dividend_date <= today()
              AND toUInt16(frequency) > 0
            GROUP BY id
        )
        GROUP BY fund_ticker
    )
SELECT
    p.ticker                                                       AS fund,
    round((100 * p.ttm_cash) / x.last_close, 2)                    AS ttm_distribution_yield_pct,
    round((100 * p.latest_cash * p.pays_per_year) / x.last_close, 2) AS indicated_yield_pct,
    round(abs(((100 * p.latest_cash * p.pays_per_year) / x.last_close)
              - ((100 * p.ttm_cash) / x.last_close)), 2)           AS gap_pct
FROM paid AS p
INNER JOIN px AS x ON x.ticker = p.ticker
ORDER BY gap_pct DESC, fund ASC