Where the Eurex close lands in the US day: FEZ volume share by half hour (ET), trailing two months

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.

SELECT
    cal.et_time                                                    AS et_time,
    round(100 * coalesce(toFloat64(t.shares), 0)
              / toFloat64((SELECT sum(toFloat64(size))
                           FROM global_markets.stocks_trades
                           WHERE ticker = 'FEZ'
                             AND sip_timestamp >= today() - 60
                             AND sip_timestamp <  today() - 2
                             AND NOT hasAny(conditions, [15, 16, 38]))), 2)   AS share_pct,
    if(cal.et_time = '11:30', 'Eurex close (17:30 CET)', '')       AS marker
FROM
(
    SELECT formatDateTime(toDateTime('2000-01-03 04:00:00', 'UTC') + 1800 * arrayJoin(range(32)), '%H:%i', 'UTC') AS et_time
) AS cal
LEFT JOIN
(
    SELECT
        formatDateTime(toStartOfInterval(toTimeZone(sip_timestamp, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
        sum(toFloat64(size))                                                                                        AS shares
    FROM global_markets.stocks_trades
    WHERE ticker = 'FEZ'
      AND sip_timestamp >= today() - 60
      AND sip_timestamp <  today() - 2
      AND NOT hasAny(conditions, [15, 16, 38])
    GROUP BY et_time
) AS t ON t.et_time = cal.et_time
ORDER BY et_time