2,170 answered market questions

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           sum(toFloat64(volume)) AS day_shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MU'
      AND window_start >= toDateTime('2025-11-01 00:00:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-11 00:00:00', 'America/New_York')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY et_date
),
ranked AS (
    SELECT et_date, day_shares,
           day_shares / avg(day_shares) OVER (ORDER BY et_date ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS shock_ratio,
           row_number() OVER (ORDER BY et_date) AS rn
    FROM daily
),
biggest AS (
    SELECT et_date, day_shares, shock_ratio
    FROM ranked
    WHERE rn > 20 AND et_date >= toDate('2026-01-01')
    ORDER BY shock_ratio DESC, et_date ASC
    LIMIT 1
),
before AS (
    SELECT avg(day_shares) AS adv FROM (
        SELECT day_shares FROM daily WHERE et_date < (SELECT et_date FROM biggest) ORDER BY et_date DESC LIMIT 20
    )
),
after AS (
    SELECT avg(day_shares) AS adv FROM (
        SELECT day_shares FROM daily WHERE et_date >= (SELECT et_date FROM biggest) ORDER BY et_date ASC LIMIT 20
    )
)
SELECT formatDateTime((SELECT et_date FROM biggest), '%Y-%m-%d') AS event_date,
       round((SELECT day_shares FROM biggest) / 1e6, 1) AS event_day_shares_m,
       round((SELECT adv FROM before) / 1e6, 1) AS adv_20_before_m,
       round((SELECT shock_ratio FROM biggest), 1) AS event_vs_prior_adv_x,
       round((SELECT adv FROM after) / 1e6, 1) AS adv_20_after_m,
       round(100 * ((SELECT adv FROM after) / (SELECT adv FROM before) - 1), 0) AS adv_shift_pct