premarket_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
    (
        SELECT max(ex_dividend_date)
        FROM global_markets.stocks_dividends
        WHERE ticker = 'KO'
          AND cash_amount > 0
          AND ex_dividend_date BETWEEN today() - 200 AND today() - 3
    ) AS ex_date,
    (
        SELECT max(toFloat64(cash_amount))
        FROM global_markets.stocks_dividends
        WHERE ticker = 'KO'
          AND ex_dividend_date = ex_date
    ) AS dividend_usd,
    (
        SELECT toFloat64(argMax(close, date))
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'KO'
          AND date >= today() - 220
          AND date < ex_date
    ) AS prior_close
SELECT
    formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 15 MINUTE), '%H:%i') AS et_time,
    formatDateTime(ex_date, '%b %e, %Y')                                                    AS ex_dividend_label,
    round(100 * (avg(toFloat64(close)) - prior_close) / prior_close, 2)                     AS vs_prior_close_pct,
    round(100 * (avg(toFloat64(close)) - (prior_close - dividend_usd))
              / (prior_close - dividend_usd), 2)                                            AS vs_adjusted_close_pct
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'KO'
  AND window_start >= today() - 210
  AND toDate(toTimeZone(window_start, 'America/New_York')) = ex_date
  AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
       + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 420
  AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
       + toMinute(toTimeZone(window_start, 'America/New_York'))) < 600
  AND volume > 0
GROUP BY et_time, ex_dividend_label
ORDER BY et_time ASC