Average move between the 4:00 p.m. close and 5:30 p.m. ET

SELECT
    ticker,
    count()                     AS friday_count,
    round(avg(drift_pct), 3)    AS avg_abs_drift_pct,
    round(max(drift_pct), 2)    AS max_abs_drift_pct
FROM
(
    SELECT
        ticker,
        session_date,
        abs(100 * (argMaxIf(px, ts, et_minute >= 960 AND et_minute < 1050)
                   / argMaxIf(px, ts, et_minute >= 570 AND et_minute < 960) - 1)) AS drift_pct
    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 avg_abs_drift_pct DESC