GME, winter 2020-21: each short interest print and the price move before it went public (as-traded prices)

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMax(toFloat64(close), window_start) AS cl
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'GME'
      AND window_start >= toDateTime('2020-12-01 00:00:00')
      AND window_start < toDateTime('2021-03-15 00:00:00')
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) BETWEEN 570 AND 959
    GROUP BY session
),
ranked AS (
    SELECT session, cl, row_number() OVER (ORDER BY session) AS n
    FROM daily
)
SELECT toString(si.settlement_date) AS settlement,
       round(si.short_interest / 1e6, 1) AS shares_short_m,
       round(r0.cl, 2) AS close_at_settlement,
       round(r8.cl, 2) AS close_8_sessions_later,
       round((r8.cl / r0.cl - 1) * 100, 1) AS move_while_pending_pct
FROM global_markets.stocks_short_interest AS si
INNER JOIN ranked AS r0 ON r0.session = si.settlement_date
INNER JOIN ranked AS r8 ON r8.n = r0.n + 8
WHERE si.ticker = 'GME'
  AND si.settlement_date >= toDate('2020-12-15')
  AND si.settlement_date <= toDate('2021-02-26')
ORDER BY si.settlement_date