3,256 answered market questions

WITH
    basket AS
    (
        SELECT
            pair.1 AS ticker,
            pair.2 AS sector
        FROM
        (
            SELECT arrayJoin([
                ('XOM', 'Energy'), ('CVX', 'Energy'), ('COP', 'Energy'), ('SLB', 'Energy'),
                ('JPM', 'Financials'), ('BAC', 'Financials'), ('GS', 'Financials'), ('BLK', 'Financials'), ('AXP', 'Financials'),
                ('JNJ', 'Health Care'), ('ABBV', 'Health Care'), ('MRK', 'Health Care'), ('AMGN', 'Health Care'), ('GILD', 'Health Care'),
                ('KO', 'Staples'), ('PG', 'Staples'), ('PEP', 'Staples'), ('COST', 'Staples'), ('CL', 'Staples'),
                ('DUK', 'Utilities'), ('SO', 'Utilities'), ('AEP', 'Utilities'), ('NEE', 'Utilities'), ('XEL', 'Utilities'),
                ('AAPL', 'Tech'), ('MSFT', 'Tech'), ('AVGO', 'Tech'), ('CSCO', 'Tech'), ('ADBE', 'Tech'),
                ('CAT', 'Industrials'), ('HON', 'Industrials'), ('UNP', 'Industrials'), ('GE', 'Industrials'), ('LMT', 'Industrials'),
                ('HD', 'Discretionary'), ('MCD', 'Discretionary'), ('AMZN', 'Discretionary'), ('TSLA', 'Discretionary'), ('SBUX', 'Discretionary'),
                ('LIN', 'Materials'), ('SHW', 'Materials'), ('NEM', 'Materials'), ('DOW', 'Materials'),
                ('VZ', 'Communication'), ('T', 'Communication'), ('CMCSA', 'Communication'), ('GOOGL', 'Communication'), ('NFLX', 'Communication'),
                ('AMT', 'Real Estate'), ('PLD', 'Real Estate'), ('PSA', 'Real Estate'), ('SPG', 'Real Estate')
            ]) AS pair
        )
    ),
    month_px AS
    (
        SELECT
            ticker,
            toStartOfMonth(date)           AS m,
            max(date)                      AS last_session,
            argMax(toFloat64(close), date) AS month_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN (SELECT ticker FROM basket)
          AND date >= toStartOfMonth(today()) - 1095
          AND date <  toStartOfMonth(today())
        GROUP BY ticker, m
    ),
    div_rate AS
    (
        SELECT
            ticker,
            ex_dividend_date,
            max(toFloat64(cash_amount) * toFloat64(frequency)) AS annual_rate
        FROM global_markets.stocks_dividends
        WHERE ticker IN (SELECT ticker FROM basket)
          AND ex_dividend_date >= toStartOfMonth(today()) - 1460
          AND frequency > 0
          AND cash_amount > 0
        GROUP BY ticker, ex_dividend_date
    ),
    name_month AS
    (
        SELECT
            p.m                                               AS m,
            p.ticker                                          AS ticker,
            round(100 * d.annual_rate / p.month_close, 3)     AS yield_pct
        FROM month_px AS p
        ASOF LEFT JOIN div_rate AS d
            ON p.ticker = d.ticker AND p.last_session >= d.ex_dividend_date
    ),
    ust AS
    (
        SELECT
            toStartOfMonth(date)                      AS m,
            round(avg(toFloat64(yield_10_year)), 2)   AS treasury_10y_pct
        FROM global_markets.treasury_yields
        WHERE date >= toStartOfMonth(today()) - 1095
          AND date <  toStartOfMonth(today())
          AND yield_10_year > 0
        GROUP BY m
    )
SELECT
    formatDateTime(n.m, '%Y-%m')                                                             AS month,
    formatDateTime(n.m, '%b %Y')                                                             AS month_label,
    round(quantileDeterministicIf(0.5)(n.yield_pct, cityHash64(n.ticker), n.yield_pct > 0), 2) AS basket_median_yield_pct,
    any(t.treasury_10y_pct)                                                                  AS treasury_10y_pct
FROM name_month AS n
INNER JOIN ust AS t ON t.m = n.m
GROUP BY n.m
HAVING countIf(n.yield_pct > 0) > 0
ORDER BY n.m