第612条半美分最小报价单位：哪些股票符合条件

WITH per_second AS
(
    SELECT
        ticker,
        toDateTime(sip_timestamp)  AS quote_second,
        max(toFloat64(bid_price))  AS best_bid,
        min(toFloat64(ask_price))  AS best_ask
    FROM global_markets.cache_stocks_quotes
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'PFE', 'F', 'GS')
      AND sip_timestamp >= '2026-08-05 13:30:00'
      AND sip_timestamp <  '2026-08-05 20:00:00'
      AND bid_price > 0
      AND ask_price > 0
    GROUP BY ticker, quote_second
)
SELECT
    ticker                                                          AS symbol,
    round(avg(best_ask - best_bid) * 100, 2)                        AS avg_quoted_spread_cents,
    round(100 * countIf(best_ask - best_bid < 0.011) / count(), 1)  AS time_at_penny_floor_pct
FROM per_second
WHERE best_ask > best_bid
GROUP BY ticker
ORDER BY avg_quoted_spread_cents ASC

SELECT
    ticker                                                  AS symbol,
    concat('$', toString(round(toFloat64(max(close)), 2)))  AS closing_price,
    round(100 / toFloat64(max(close)), 2)                   AS one_cent_in_bps
FROM global_markets.stocks_daily_aggs
WHERE date = '2026-08-05'
  AND ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'PFE', 'F', 'GS')
GROUP BY ticker
ORDER BY one_cent_in_bps DESC

WITH per_second AS
(
    SELECT
        ticker,
        toDateTime(sip_timestamp)  AS quote_second,
        max(toFloat64(bid_price))  AS best_bid,
        min(toFloat64(ask_price))  AS best_ask
    FROM global_markets.cache_stocks_quotes
    WHERE ticker IN ('AAPL', 'F', 'GS')
      AND sip_timestamp >= '2026-08-05 13:30:00'
      AND sip_timestamp <  '2026-08-05 20:00:00'
      AND bid_price > 0
      AND ask_price > 0
    GROUP BY ticker, quote_second
),
graded AS
(
    SELECT
        ticker,
        least(toUInt16(round((best_ask - best_bid) * 100)), 5) AS spread_cents
    FROM per_second
    WHERE best_ask > best_bid
),
totals AS
(
    SELECT
        ticker,
        count() AS quoted_seconds
    FROM graded
    GROUP BY ticker
),
shares AS
(
    SELECT
        g.ticker                        AS ticker,
        g.spread_cents                  AS spread_cents,
        count() / any(t.quoted_seconds) AS time_share
    FROM graded AS g
    INNER JOIN totals AS t ON t.ticker = g.ticker
    GROUP BY g.ticker, g.spread_cents
)
SELECT
    if(spread_cents >= 5,
       '5 cents or wider',
       concat(toString(spread_cents), if(spread_cents = 1, ' cent', ' cents'))) AS quoted_spread,
    round(100 * sumIf(time_share, ticker = 'AAPL'), 1) AS aapl_pct,
    round(100 * sumIf(time_share, ticker = 'F'), 1)    AS f_pct,
    round(100 * sumIf(time_share, ticker = 'GS'), 1)   AS gs_pct
FROM shares
GROUP BY spread_cents
ORDER BY spread_cents ASC

WITH per_second AS
(
    SELECT
        ticker,
        toDateTime(sip_timestamp, 'America/New_York') AS quote_second,
        max(toFloat64(bid_price))                     AS best_bid,
        min(toFloat64(ask_price))                     AS best_ask
    FROM global_markets.cache_stocks_quotes
    WHERE ticker IN ('AAPL', 'F')
      AND sip_timestamp >= '2026-08-05 13:00:00'
      AND sip_timestamp <  '2026-08-05 20:30:00'
      AND bid_price > 0
      AND ask_price > 0
    GROUP BY ticker, quote_second
)
SELECT
    formatDateTime(toStartOfInterval(quote_second, INTERVAL 15 MINUTE), '%H:%i') AS et_time,
    round(100 * countIf(ticker = 'AAPL' AND best_ask - best_bid < 0.011)
              / countIf(ticker = 'AAPL'), 1) AS aapl_floor_pct,
    round(100 * countIf(ticker = 'F' AND best_ask - best_bid < 0.011)
              / countIf(ticker = 'F'), 1)    AS f_floor_pct
FROM per_second
WHERE best_ask > best_bid
GROUP BY toStartOfInterval(quote_second, INTERVAL 15 MINUTE)
HAVING countIf(ticker = 'AAPL') > 0 AND countIf(ticker = 'F') > 0
ORDER BY toStartOfInterval(quote_second, INTERVAL 15 MINUTE) ASC