2,178 answered market questions

SELECT m.ticker AS ticker,
       round(m.latest_payment, 4) AS monthly_payment_usd,
       round(100 * m.latest_payment * 12 / l.last_close, 2) AS annual_yield_pct,
       round(dateDiff('day', s.streak_start, s.last_ex) / 365.25, 1) AS years_paying_monthly,
       round(l.adv_usd / 1e6, 1) AS avg_daily_traded_musd,
       formatDateTime(l.last_session, '%b %e') AS priced_through
FROM (
    SELECT ticker, argMax(cash_amount, ex_dividend_date) AS latest_payment
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date > today() - 365
      AND ex_dividend_date <= today()
      AND cash_amount > 0
      AND distribution_type = 'recurring'
      AND ticker NOT IN ('SPCX')
      AND ticker NOT IN ('KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 400 AND today())
    GROUP BY ticker
    HAVING count() BETWEEN 10 AND 14
       AND argMax(frequency, ex_dividend_date) = 12
) AS m
INNER JOIN (
    SELECT ticker,
           maxIf(ed, gap_days > 70) AS streak_start,
           max(ed) AS last_ex
    FROM (
        SELECT ticker, ex_dividend_date AS ed,
               dateDiff('day',
                        lagInFrame(ex_dividend_date) OVER (PARTITION BY ticker ORDER BY ex_dividend_date),
                        ex_dividend_date) AS gap_days
        FROM (
            SELECT ticker, ex_dividend_date
            FROM global_markets.stocks_dividends
            WHERE distribution_type = 'recurring' AND cash_amount > 0 AND ex_dividend_date <= today()
            GROUP BY ticker, ex_dividend_date
        )
    )
    GROUP BY ticker
) AS s ON s.ticker = m.ticker
INNER JOIN (
    SELECT ticker,
           avg(dollar_vol) AS adv_usd,
           argMax(close_px, d) AS last_close,
           max(d) AS last_session,
           count() AS sessions
    FROM (
        SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               sum(toFloat64(close) * volume) AS dollar_vol,
               argMax(toFloat64(close), window_start) AS close_px
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= toDateTime(today() - 32)
          AND toDate(toTimeZone(window_start, 'America/New_York')) < today()
          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 ticker, d
    )
    GROUP BY ticker
    HAVING sessions >= 15 AND adv_usd >= 1000000 AND last_close > 0
) AS l ON l.ticker = m.ticker
INNER JOIN (
    SELECT ticker
    FROM global_markets.stocks_ratios
    GROUP BY ticker
    HAVING argMax(market_cap, date) > 0
) AS f ON f.ticker = m.ticker
ORDER BY l.adv_usd DESC
LIMIT 60