Which venue held the national best bid in AAPL, June 17 2026, 10:00 to 11:30 ET

WITH venue_snapshot AS
(
    SELECT
        toStartOfSecond(sip_timestamp) AS snap,
        toUInt32(bid_exchange)         AS exchange_id,
        max(toFloat64(bid_price))      AS venue_best_bid
    FROM global_markets.cache_stocks_quotes
    WHERE ticker = 'AAPL'
      AND sip_timestamp >= '2026-06-17 14:00:00'
      AND sip_timestamp <  '2026-06-17 15:30:00'
      AND bid_price > 0
    GROUP BY snap, exchange_id
),
national_snapshot AS
(
    SELECT
        snap,
        max(venue_best_bid) AS national_best_bid
    FROM venue_snapshot
    GROUP BY snap
),
tally AS
(
    SELECT
        v.exchange_id                                   AS exchange_id,
        countIf(v.venue_best_bid >= n.national_best_bid) AS snapshots_at_national_best_bid,
        count()                                          AS snapshots_quoting
    FROM venue_snapshot AS v
    INNER JOIN national_snapshot AS n ON n.snap = v.snap
    GROUP BY exchange_id
)
SELECT
    if(empty(x.venue_name), concat('Venue ', toString(t.exchange_id)), x.venue_name) AS venue,
    t.snapshots_at_national_best_bid AS snapshots_at_national_best_bid,
    t.snapshots_quoting              AS snapshots_quoting
FROM tally AS t
LEFT JOIN
(
    SELECT
        toUInt32(id) AS exchange_id,
        any(name)    AS venue_name
    FROM global_markets.stocks_exchanges
    WHERE asset_class = 'stocks'
    GROUP BY exchange_id
) AS x ON x.exchange_id = t.exchange_id
ORDER BY snapshots_at_national_best_bid DESC
LIMIT 20