SPY's update-weighted average quoted spread: July 7 ranked against the trailing month (rank 1 = tightest)

SELECT
    round(anyIf(avg_spread_cents, d = toDate('2026-07-07')), 3) AS jul7_avg_spread_cents,
    arrayCount(x -> x < anyIf(avg_spread_cents, d = toDate('2026-07-07')), groupArrayIf(avg_spread_cents, d != toDate('2026-07-07'))) + 1 AS tightness_rank,
    count() AS sessions_compared,
    round(min(avg_spread_cents), 3) AS tightest_session_cents,
    round(max(avg_spread_cents), 3) AS widest_session_cents,
    toString(min(d)) AS first_session,
    sum(dropped_invalid) AS dropped_invalid_quotes
FROM (
    SELECT toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS d,
           avgIf(toFloat64(ask_price - bid_price), bid_price > 0 AND ask_price >= bid_price) * 100 AS avg_spread_cents,
           countIf(NOT (bid_price > 0 AND ask_price >= bid_price)) AS dropped_invalid
    FROM global_markets.cache_stocks_quotes
    WHERE ticker = 'SPY'
      AND sip_timestamp >= toDateTime('2026-06-05 00:00:00')
      AND sip_timestamp < toDateTime('2026-07-08 00:00:00')
      AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
    GROUP BY d
)