引けの売買高はどの程度か

WITH
    auction AS
    (
        SELECT
            toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session,
            ticker,
            max(size)                                             AS auction_shares
        FROM global_markets.stocks_trades
        WHERE ticker IN ('KO', 'JNJ', 'XOM', 'WMT', 'AAPL', 'MSFT', 'COST', 'CSCO')
          AND toDate(sip_timestamp) IN ('2026-08-11', '2026-08-21', '2026-09-18', '2026-09-23')
          AND toHour(toTimeZone(sip_timestamp, 'America/New_York')) = 16
          AND toMinute(toTimeZone(sip_timestamp, 'America/New_York')) = 0
        GROUP BY session, ticker
    ),
    day_volume AS
    (
        SELECT
            date        AS session,
            ticker,
            max(volume) AS day_shares
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('KO', 'JNJ', 'XOM', 'WMT', 'AAPL', 'MSFT', 'COST', 'CSCO')
          AND date IN ('2026-08-11', '2026-08-21', '2026-09-18', '2026-09-23')
        GROUP BY session, ticker
    )
SELECT
    multiIf(
        session = '2026-08-11', 'quiet Tuesday, Aug 11',
        session = '2026-08-21', 'monthly opex Friday, Aug 21',
        session = '2026-09-18', 'quarterly rebalance Friday, Sep 18',
                                'ordinary Wednesday, Sep 23')                 AS label,
    round(100 * sumIf(toFloat64(auction_shares), ticker IN ('KO', 'JNJ', 'XOM', 'WMT'))
              / sumIf(toFloat64(day_shares),     ticker IN ('KO', 'JNJ', 'XOM', 'WMT')), 2)             AS nyse_listed_pct,
    round(100 * sumIf(toFloat64(auction_shares), ticker IN ('AAPL', 'MSFT', 'COST', 'CSCO'))
              / sumIf(toFloat64(day_shares),     ticker IN ('AAPL', 'MSFT', 'COST', 'CSCO')), 2)        AS nasdaq_listed_pct
FROM auction
INNER JOIN day_volume USING (session, ticker)
GROUP BY session
ORDER BY session

SELECT
    toString(any(id)) AS condition_id,
    name              AS condition_name,
    any(abbreviation) AS abbreviation,
    any(type)         AS condition_type
FROM global_markets.stocks_condition_codes
WHERE asset_class = 'stocks'
  AND name ILIKE '%clos%'
GROUP BY name
ORDER BY condition_id

WITH
    closing_print AS
    (
        SELECT
            toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session,
            max(size)                                             AS auction_shares
        FROM global_markets.stocks_trades
        WHERE ticker = 'KO'
          AND sip_timestamp >= toDateTime('2026-08-03 00:00:00')
          AND sip_timestamp <  toDateTime('2026-10-01 00:00:00')
          AND toHour(toTimeZone(sip_timestamp, 'America/New_York')) = 16
          AND toMinute(toTimeZone(sip_timestamp, 'America/New_York')) = 0
        GROUP BY session
    ),
    day_volume AS
    (
        SELECT
            date        AS session,
            max(volume) AS day_shares
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'KO'
          AND date >= '2026-08-03'
          AND date <  '2026-10-01'
        GROUP BY session
    )
SELECT
    session,
    round(100 * toFloat64(auction_shares) / toFloat64(day_shares), 2) AS auction_pct,
    round(toFloat64(day_shares) / 1e6, 1)                             AS day_volume_millions
FROM closing_print
INNER JOIN day_volume USING (session)
ORDER BY session

SELECT
    formatDateTime(toStartOfMinute(toTimeZone(sip_timestamp, 'America/New_York')), '%H:%i') AS et_time,
    toUInt64(sum(size))                                                                     AS volume,
    formatReadableQuantity(toFloat64(sum(size)))                                            AS volume_label
FROM global_markets.stocks_trades
WHERE ticker = 'KO'
  AND sip_timestamp >= toDateTime('2026-09-23 19:50:00')
  AND sip_timestamp <  toDateTime('2026-09-23 20:01:00')
GROUP BY et_time
ORDER BY et_time

WITH
    (
        SELECT toFloat64(max(volume))
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'KO'
          AND date = '2026-09-23'
    ) AS day_shares
SELECT
    formatDateTime(toTimeZone(sip_timestamp, 'America/New_York'), '%H:%i:%S') AS et_clock,
    toUInt64(size)                                                           AS shares,
    round(100 * toFloat64(size) / day_shares, 2)                             AS pct_of_day_volume,
    formatReadableQuantity(toFloat64(size))                                  AS shares_label
FROM global_markets.stocks_trades
WHERE ticker = 'KO'
  AND sip_timestamp >= toDateTime('2026-09-23 13:00:00')
  AND sip_timestamp <  toDateTime('2026-09-24 01:00:00')
ORDER BY size DESC
LIMIT 8