openingsvenster_versus_slot

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 venster AS
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS sessie,
        ticker,
        avg(toFloat64(close))                                AS gemiddelde
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('ASML', 'SPY')
      AND window_start >= today() - 100
      AND window_start <  today() - 2
      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'))) <= 600
    GROUP BY sessie, ticker
),
slot AS
(
    SELECT
        date                  AS sessie,
        ticker,
        toFloat64(any(close)) AS slotkoers
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('ASML', 'SPY')
      AND date >= today() - 100
    GROUP BY sessie, ticker
),
afwijking AS
(
    SELECT
        v.sessie AS sessie,
        v.ticker AS ticker,
        round(10000 * (v.gemiddelde - s.slotkoers) / s.slotkoers, 1) AS bps
    FROM venster AS v
    INNER JOIN slot AS s ON s.sessie = v.sessie AND s.ticker = v.ticker
)
SELECT
    toString(sessie) AS date,
    concat(toString(toDayOfMonth(sessie)), ' ',
           arrayElement(['jan', 'feb', 'mrt', 'apr', 'mei', 'jun',
                         'jul', 'aug', 'sep', 'okt', 'nov', 'dec'], toMonth(sessie))) AS sessie_label,
    round(anyIf(bps, ticker = 'ASML'), 1) AS asml_bps,
    round(anyIf(bps, ticker = 'SPY'), 1)  AS spy_bps
FROM afwijking
GROUP BY sessie
HAVING countIf(ticker = 'ASML') > 0 AND countIf(ticker = 'SPY') > 0
ORDER BY sessie