monthly_payers

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH monthly AS (
    SELECT
        ticker,
        ex_dividend_date,
        max(cash_amount) AS amount
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= addMonths(toStartOfMonth(today()), -12)
      AND ex_dividend_date <  toStartOfMonth(today())
      AND currency = 'USD'
      AND cash_amount > 0
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, ex_dividend_date
),
payers AS (
    SELECT
        ticker,
        round(toFloat64(avg(amount)), 4) AS avg_payment_usd,
        round(toFloat64(sum(amount)), 4) AS annual_usd
    FROM monthly
    GROUP BY ticker
    HAVING count() = 12
       AND countDistinct(toStartOfMonth(ex_dividend_date)) = 12
),
last_price AS (
    SELECT
        ticker,
        toFloat64(argMax(close, date)) AS last_close
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 30
      AND ticker IN (SELECT ticker FROM payers)
    GROUP BY ticker
),
filers AS (
    SELECT DISTINCT arrayJoin(tickers) AS filer_ticker
    FROM global_markets.stocks_income_statements
    WHERE period_end >= addMonths(today(), -30)
)
SELECT
    p.ticker                                                         AS ticker,
    if(p.ticker IN (SELECT filer_ticker FROM filers), 'есть', 'нет') AS issuer_reports,
    p.avg_payment_usd                                                AS avg_payment_usd,
    p.annual_usd                                                     AS annual_usd
FROM payers AS p
INNER JOIN last_price AS x ON x.ticker = p.ticker
WHERE x.last_close >= 5
  AND 100 * p.annual_usd / x.last_close BETWEEN 2 AND 25
ORDER BY 100 * p.annual_usd / x.last_close DESC
LIMIT 12