2,309 answered market questions

SELECT
    p.et_clock                                          AS et_clock,
    p.price                                             AS price,
    p.shares_mn                                         AS shares_mn,
    p.condition_codes                                   AS condition_codes,
    p.venue                                             AS venue,
    round(p.shares / m.median_trade_shares / 1000, 1)   AS x_median_trade_k
FROM
(
    SELECT
        t.et_clock          AS et_clock,
        t.price             AS price,
        t.shares            AS shares,
        t.shares_mn         AS shares_mn,
        t.condition_codes   AS condition_codes,
        t.seq               AS seq,
        e.venue             AS venue
    FROM
    (
        SELECT
            substring(toString(toTimeZone(sip_timestamp, 'America/New_York')), 12, 12) AS et_clock,
            round(toFloat64(price), 2)          AS price,
            toFloat64(size)                     AS shares,
            round(toFloat64(size) / 1e6, 3)     AS shares_mn,
            toString(conditions)                AS condition_codes,
            toInt32(exchange)                   AS exch,
            toUInt64(sequence_number)           AS seq
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= toDateTime('2026-09-10 20:00:00', 'UTC')
          AND sip_timestamp <  toDateTime('2026-09-10 20:00:10', 'UTC')
        ORDER BY shares DESC, sip_timestamp
        LIMIT 6
    ) AS t
    LEFT JOIN
    (
        SELECT toInt32(id) AS exch, any(name) AS venue
        FROM global_markets.stocks_exchanges
        GROUP BY exch
    ) AS e ON e.exch = t.exch
) AS p
CROSS JOIN
(
    SELECT quantileExact(0.5)(toFloat64(size)) AS median_trade_shares
    FROM global_markets.stocks_trades
    WHERE ticker = 'AAPL'
      AND sip_timestamp >= toDateTime('2026-09-10 08:00:00', 'UTC')
      AND sip_timestamp <  toDateTime('2026-09-11 00:00:00', 'UTC')
) AS m
ORDER BY p.shares DESC, p.seq