2,170 answered market questions

WITH
    (
        SELECT (round(count() / 1e6, 2),
                round(100.0 * countIf(bid_price > 0 AND ask_price > 0 AND ask_price > bid_price) / count(), 2),
                countIf(bid_price > 0 AND ask_price > 0 AND ask_price = bid_price),
                countIf(bid_price > 0 AND ask_price > 0 AND ask_price < bid_price),
                countIf(bid_price <= 0 OR ask_price <= 0),
                countIf(bid_price > 0 AND ask_price > 0 AND ask_price < bid_price AND sip_timestamp < toDateTime64('2026-06-13 00:00:00', 9)),
                countIf(bid_price > 0 AND ask_price > 0 AND ask_price < bid_price AND sip_timestamp >= toDateTime64('2026-06-30 00:00:00', 9)))
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'SPCX'
          AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
    ) AS quote_census
SELECT
    round(count() / 1e6, 2) AS prints_m,
    quantileDeterministic(0.5)(toFloat64(size), toUInt64(abs(sequence_number))) AS median_print_shares,
    round(avg(toFloat64(size)), 1) AS avg_print_shares,
    round(100.0 * countIf(size < 100) / count(), 1) AS odd_lot_pct_of_prints,
    round(100.0 * countIf(toFloat64(size) != round(toFloat64(size))) / count(), 2) AS fractional_pct_of_prints,
    quote_census.1 AS nbbo_updates_m,
    quote_census.2 AS clean_two_sided_pct,
    quote_census.3 AS locked_updates,
    quote_census.4 AS crossed_updates,
    quote_census.5 AS one_sided_or_empty_updates,
    quote_census.6 AS crossed_first_session,
    quote_census.7 AS crossed_final_session
FROM global_markets.stocks_trades
WHERE ticker = 'SPCX'
  AND sip_timestamp >= toDateTime64('2026-06-12 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)