2,170 answered market questions

SELECT
    ticker,
    count()                                      AS expirations_measured,
    countIf(floor(close_px) != floor(post_px))   AS dollar_line_crossings
FROM
(
    SELECT
        ticker,
        session_date,
        argMaxIf(px, ts, et_minute >= 570 AND et_minute < 960)  AS close_px,
        argMaxIf(px, ts, et_minute >= 960 AND et_minute < 1050) AS post_px
    FROM
    (
        SELECT
            ticker,
            toDate(toTimeZone(window_start, 'America/New_York'))       AS session_date,
            toHour(toTimeZone(window_start, 'America/New_York')) * 60
              + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_minute,
            toFloat64(close)                                           AS px,
            window_start                                               AS ts
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('AAPL', 'KO', 'MSFT', 'NVDA', 'SPY', 'TSLA')
          AND window_start >= today() - 730
          AND toDayOfWeek(toDate(toTimeZone(window_start, 'America/New_York'))) = 5
          AND toDayOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) BETWEEN 15 AND 21
    )
    GROUP BY ticker, session_date
    HAVING countIf(et_minute >= 960 AND et_minute < 1050) > 0
       AND countIf(et_minute >= 570 AND et_minute < 960) > 0
)
GROUP BY ticker
ORDER BY dollar_line_crossings DESC, ticker