2,170 answered market questions

SELECT ticker,
       typical_share_price,
       pct_quotes_at_one_cent,
       round(100 / typical_share_price, 2) AS one_cent_in_bps,
       typical_spread_bps,
       round(abs(typical_spread_bps - 100 / typical_share_price), 2) AS dist_from_floor_bps
FROM (
    SELECT ticker,
           round(quantileExactIf(0.5)((toFloat64(ask_price) + toFloat64(bid_price)) / 2, bid_price > 0 AND ask_price > bid_price), 2) AS typical_share_price,
           round(100 * countIf(bid_price > 0 AND ask_price > bid_price AND abs(toFloat64(ask_price - bid_price) - 0.01) < 0.001) / countIf(bid_price > 0 AND ask_price > bid_price), 1) AS pct_quotes_at_one_cent,
           round(quantileExactIf(0.5)(toFloat64(ask_price - bid_price) / (toFloat64(ask_price + bid_price) / 2), bid_price > 0 AND ask_price > bid_price) * 10000, 2) AS typical_spread_bps
    FROM global_markets.cache_stocks_quotes
    WHERE ticker IN ('SPY', 'AAPL', 'KO', 'F', 'SOXS')
      AND sip_timestamp >= toDateTime(today() - 10)
      AND sip_timestamp < toDateTime(today() - 3)
      AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
    GROUP BY ticker
)
ORDER BY typical_share_price DESC