למה מניות פותחות בפער מעלה או מטה במהלך הלילה

SELECT count() AS sessions,
       round(avg(abs(100 * (rth_open - prior_close) / prior_close)), 2) AS avg_abs_overnight_gap_pct,
       round(avg(abs(100 * (rth_close - rth_open) / rth_open)), 2) AS avg_abs_intraday_move_pct,
       countIf(abs(100 * (rth_open - prior_close) / prior_close) >= 0.5) AS gaps_of_half_pct_or_more
FROM (SELECT day, rth_open, rth_close, lagInFrame(rth_close) OVER (ORDER BY day) AS prior_close
    FROM (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
               argMinIf(toFloat64(open), window_start, rth) AS rth_open,
               argMaxIf(toFloat64(close), window_start, rth) AS rth_close
        FROM (
            SELECT window_start, open, close,
                   toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
                   AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York') AS rth
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'SPY'
              AND window_start >= '2025-12-29 04:00:00'
              AND window_start < '2026-07-01 08:00:00'
        )
        GROUP BY day
    ))
WHERE day >= '2026-01-01' AND prior_close > 0 AND isFinite(prior_close)

SELECT ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'][dow] AS weekday,
       count() AS sessions,
       round(avg(abs(gap_pct)), 2) AS avg_abs_gap_pct,
       round(quantileDeterministic(0.9)(abs(gap_pct), toUInt64(toYYYYMMDD(day))), 2) AS p90_abs_gap_pct
FROM (
    SELECT day, toDayOfWeek(day) AS dow,
           100 * (rth_open - prior_close) / prior_close AS gap_pct
    FROM (
        SELECT day, rth_open, lagInFrame(rth_close) OVER (ORDER BY day) AS prior_close
        FROM (
            SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
                   argMinIf(toFloat64(open), window_start, rth) AS rth_open,
                   argMaxIf(toFloat64(close), window_start, rth) AS rth_close
            FROM (
            SELECT window_start, open, close,
                   toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
                   AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York') AS rth
                FROM global_markets.delayed_stocks_minute_aggs
                WHERE ticker = 'SPY'
                  AND window_start >= '2023-12-28 04:00:00'
                  AND window_start < '2026-07-01 08:00:00'
            )
            GROUP BY day
        )
    )
    WHERE day >= '2024-01-01' AND prior_close > 0 AND isFinite(prior_close)
)
WHERE dow <= 5
GROUP BY dow
ORDER BY dow

SELECT concat(monthName(day), ' ', toString(toDayOfMonth(day)), ', ', toString(toYear(day))) AS session,
       round(100 * (rth_open - prior_close) / prior_close, 2) AS overnight_gap_pct,
       round(100 * (rth_close - rth_open) / rth_open, 2) AS rest_of_day_pct
FROM (SELECT day, rth_open, rth_close, lagInFrame(rth_close) OVER (ORDER BY day) AS prior_close
    FROM (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
               argMinIf(toFloat64(open), window_start, rth) AS rth_open,
               argMaxIf(toFloat64(close), window_start, rth) AS rth_close
        FROM (
            SELECT window_start, open, close,
                   toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
                   AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York') AS rth
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'SPY'
              AND window_start >= '2025-12-29 04:00:00'
              AND window_start < '2026-07-01 08:00:00'
        )
        GROUP BY day
    ))
WHERE day >= '2026-01-01' AND prior_close > 0 AND isFinite(prior_close)
ORDER BY abs(overnight_gap_pct) DESC, day
LIMIT 6

SELECT formatDateTime(toTimeZone(published_utc, 'America/New_York'), '%b %d %H:%i') AS et_published,
       JSONExtractString(toString(publisher), 'name') AS source,
       substring(title, 1, 80) AS headline
FROM global_markets.stocks_news
WHERE published_utc >= toDateTime('2026-04-07 16:00:00', 'America/New_York')
  AND published_utc < toDateTime('2026-04-08 09:30:00', 'America/New_York')
  AND has(tickers, 'SPY')
ORDER BY published_utc
LIMIT 10

SELECT ticker,
       count() AS sessions,
       round(avg(abs(gap_pct)), 2) AS avg_abs_gap_pct,
       round(argMax(gap_pct, (abs(gap_pct), day)), 2) AS biggest_gap_pct,
       concat(monthName(argMax(day, (abs(gap_pct), day))), ' ', toString(toDayOfMonth(argMax(day, (abs(gap_pct), day)))), ', ', toString(toYear(argMax(day, (abs(gap_pct), day))))) AS biggest_gap_day
FROM (
    SELECT ticker, day, 100 * (rth_open - prior_close) / prior_close AS gap_pct
    FROM (
        SELECT ticker, day, rth_open,
               lagInFrame(rth_close) OVER (PARTITION BY ticker ORDER BY day) AS prior_close
        FROM (
            SELECT ticker, toDate(toTimeZone(window_start, 'America/New_York')) AS day,
                   argMinIf(toFloat64(open), window_start, rth) AS rth_open,
                   argMaxIf(toFloat64(close), window_start, rth) AS rth_close
            FROM (
                SELECT ticker, window_start, open, close,
                       toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
                       AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York') AS rth
                FROM global_markets.delayed_stocks_minute_aggs
                WHERE ticker IN ('SPY', 'KO', 'AAPL', 'MSFT', 'NVDA', 'TSLA')
                  AND window_start >= '2025-12-29 04:00:00'
                  AND window_start < '2026-07-01 08:00:00'
            )
            GROUP BY ticker, day
        )
    )
    WHERE day >= '2026-01-01' AND prior_close > 0 AND isFinite(prior_close)
)
GROUP BY ticker
ORDER BY indexOf(['SPY', 'KO', 'AAPL', 'MSFT', 'NVDA', 'TSLA'], ticker)

WITH day_vol AS (
    SELECT sum(toFloat64(volume)) AS rth_vol
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MSFT'
      AND window_start >= toDateTime('2026-01-29 09:30:00', 'America/New_York')
      AND window_start < toDateTime('2026-01-29 16:00:00', 'America/New_York')
),
prior AS (
    SELECT round(argMax(toFloat64(close), window_start), 2) AS prior_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MSFT'
      AND window_start >= toDateTime('2026-01-28 09:30:00', 'America/New_York')
      AND window_start < toDateTime('2026-01-28 16:00:00', 'America/New_York')
),
buckets AS (
    SELECT toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 minute) AS bucket,
           round(argMax(toFloat64(close), window_start), 2) AS price,
           sum(toFloat64(volume)) AS shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MSFT'
      AND window_start >= toDateTime('2026-01-29 04:00:00', 'America/New_York')
      AND window_start < toDateTime('2026-01-29 09:30:00', 'America/New_York')
    GROUP BY bucket
)
SELECT formatDateTime(bucket, '%H:%i') AS et_time,
       price,
       (SELECT prior_close FROM prior) AS prior_close,
       round(100 * (price - (SELECT prior_close FROM prior)) / (SELECT prior_close FROM prior), 2) AS vs_prior_close_pct,
       round(abs(100 * (price - (SELECT prior_close FROM prior)) / (SELECT prior_close FROM prior)), 2) AS pct_below_prior_close,
       round(shares / 1000) AS shares_thousands,
       round(sum(shares) OVER (ORDER BY bucket) / 1000000, 2) AS cum_shares_millions,
       round(100 * sum(shares) OVER (ORDER BY bucket) / (SELECT rth_vol FROM day_vol), 2) AS cum_pct_of_regular_volume
FROM buckets
ORDER BY bucket

WITH prior AS (
    SELECT round(argMax(toFloat64(close), window_start), 2) AS prior_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MSFT'
      AND window_start >= toDateTime('2026-01-28 09:30:00', 'America/New_York')
      AND window_start < toDateTime('2026-01-28 16:00:00', 'America/New_York')
),
quotes AS (
    SELECT toFloat64(substring(ticker, 14, 8)) / 1000 AS strike,
           substring(ticker, 13, 1) AS cp,
           round(argMax((bid_price + ask_price) / 2, sip_timestamp), 2) AS mid
    FROM global_markets.cache_options_quotes
    WHERE ticker LIKE 'O:MSFT260130%'
      AND sip_timestamp >= toDateTime('2026-01-28 15:45:00', 'America/New_York')
      AND sip_timestamp < toDateTime('2026-01-28 16:00:00', 'America/New_York')
      AND bid_price > 0
      AND ask_price > 0
    GROUP BY ticker
)
SELECT concat('$', toString(strike)) AS strike_price,
       maxIf(mid, cp = 'C') AS call_mid,
       maxIf(mid, cp = 'P') AS put_mid,
       round(maxIf(mid, cp = 'C') + maxIf(mid, cp = 'P'), 2) AS straddle_cost,
       round(100 * (maxIf(mid, cp = 'C') + maxIf(mid, cp = 'P')) / (SELECT prior_close FROM prior), 2) AS implied_move_pct
FROM quotes
WHERE abs(strike - (SELECT prior_close FROM prior)) <= 5
GROUP BY strike
HAVING countIf(cp = 'C') > 0 AND countIf(cp = 'P') > 0
ORDER BY strike