ko_ex_trace

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
    bars AS
    (
        SELECT
            toDate(date)          AS hari,
            any(toFloat64(open))  AS harga_buka,
            any(toFloat64(close)) AS harga_tutup
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'KO'
          AND date >= today() - 800
        GROUP BY hari
    ),
    bars_lag AS
    (
        SELECT
            hari,
            harga_buka,
            lagInFrame(harga_tutup, 1) OVER (ORDER BY hari ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS tutup_cum
        FROM bars
    ),
    dividen_ko AS
    (
        SELECT
            toDate(ex_dividend_date)    AS exd,
            any(toFloat64(cash_amount)) AS dividen
        FROM global_markets.stocks_dividends
        WHERE ticker = 'KO'
          AND ex_dividend_date >= today() - 760
          AND ex_dividend_date <  today() - 3
        GROUP BY exd
    )
SELECT
    toString(d.exd)                                         AS ex_date,
    concat(toString(toDayOfMonth(d.exd)), ' ',
           transform(toMonth(d.exd),
                     [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
                     ['Jan', 'Feb', 'Mar', 'Apr', 'Mei', 'Jun', 'Jul', 'Agu', 'Sep', 'Okt', 'Nov', 'Des'],
                     ''),
           ' ', toString(toYear(d.exd)))                    AS tanggal_ex,
    round(b.tutup_cum, 2)                                   AS harga_tutup_cum,
    round(b.harga_buka, 2)                                  AS harga_buka_ex,
    round(d.dividen / b.tutup_cum * 100, 2)                 AS dividen_pct,
    round((b.harga_buka / b.tutup_cum - 1) * 100, 2)        AS gap_open_pct
FROM dividen_ko AS d
INNER JOIN bars_lag AS b ON b.hari = d.exd
WHERE b.tutup_cum > 0
ORDER BY d.exd