mavuno_gawio

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 bei_hivi_sasa AS
(
    SELECT
        ticker,
        argMax(toFloat64(close), date) AS bei,
        toString(max(date))            AS tarehe_bei
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('VOO', 'SPY', 'QQQ', 'SCHD')
      AND date >= today() - 30
    GROUP BY ticker
),
gawio_miezi_12 AS
(
    SELECT
        ticker,
        sum(kiasi) AS jumla,
        count()    AS malipo
    FROM
    (
        SELECT
            ticker,
            id,
            max(ifNull(nullIf(toFloat64(split_adjusted_cash_amount), 0.), toFloat64(cash_amount))) AS kiasi
        FROM global_markets.stocks_dividends
        WHERE ticker IN ('VOO', 'SPY', 'QQQ', 'SCHD')
          AND ex_dividend_date >  today() - 365
          AND ex_dividend_date <= today()
        GROUP BY ticker, id
    )
    GROUP BY ticker
)
SELECT
    g.ticker                               AS ticker,
    b.tarehe_bei                           AS tarehe_bei,
    round(g.jumla, 4)                      AS gawio_kwa_hisa,
    toUInt8(g.malipo)                      AS malipo_mwaka,
    round(100 * g.jumla / b.bei, 2)        AS mavuno_ghafi_pct,
    round(100 * g.jumla * 0.30 / b.bei, 2) AS kodi_pct,
    round(100 * g.jumla * 0.70 / b.bei, 2) AS mavuno_halisi_pct
FROM gawio_miezi_12 AS g
INNER JOIN bei_hivi_sasa AS b ON b.ticker = g.ticker
ORDER BY mavuno_ghafi_pct DESC