3,293 answered market questions

SELECT
    b.ticker                                                                           AS ticker,
    round(100 * sum(b.open_minute)   / sum(b.continuous_shares + p.closing_print), 2) AS opening_minute_pct,
    round(100 * sum(b.midday_minute) / sum(b.continuous_shares + p.closing_print), 2) AS midday_minute_pct,
    round(100 * sum(p.closing_print) / sum(b.continuous_shares + p.closing_print), 2) AS closing_cross_pct
FROM
(
    SELECT
        ticker,
        d,
        sumIf(v, et_min = 570) AS open_minute,
        sumIf(v, et_min = 750) AS midday_minute,
        sum(v)                 AS continuous_shares
    FROM
    (
        SELECT
            ticker,
            toDate(toTimeZone(window_start, 'America/New_York'))        AS d,
            toHour(toTimeZone(window_start, 'America/New_York')) * 60
              + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_min,
            toFloat64(volume)                                           AS v
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'TSLA')
          AND window_start >= '2026-08-03 04:00:00'
          AND window_start <  '2026-09-01 04:00:00'
    )
    WHERE et_min BETWEEN 570 AND 959
    GROUP BY ticker, d
) AS b
INNER JOIN
(
    SELECT
        ticker,
        toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS d,
        toFloat64(maxIf(size, has(conditions, 8)))            AS closing_print
    FROM global_markets.stocks_trades
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'TSLA')
      AND sip_timestamp >= '2026-08-03 04:00:00'
      AND sip_timestamp <  '2026-09-01 04:00:00'
      AND toHour(toTimeZone(sip_timestamp, 'America/New_York')) = 16
      AND toMinute(toTimeZone(sip_timestamp, 'America/New_York')) < 10
    GROUP BY ticker, d
    HAVING countIf(has(conditions, 8)) > 0
) AS p ON p.ticker = b.ticker AND p.d = b.d
GROUP BY ticker
HAVING sum(b.continuous_shares) > 0
ORDER BY ticker