2,173 answered market questions

WITH multiIf(
        ticker = 'ARM',  toDate('2023-09-14'),
        ticker = 'CART', toDate('2023-09-19'),
        ticker = 'BIRK', toDate('2023-10-11'),
        ticker = 'ALAB', toDate('2024-03-20'),
        ticker = 'RDDT', toDate('2024-03-21'),
        ticker = 'RBRK', toDate('2024-04-25'),
        toDate('2024-01-01')) + 180 AS lockup_mark
SELECT
    ticker,
    round(avgIf(day_volume, session_date <  lockup_mark) / 1e6, 2) AS avg_volume_before_m,
    round(avgIf(day_volume, session_date >= lockup_mark) / 1e6, 2) AS avg_volume_after_m,
    round(100 * (avgIf(day_volume, session_date >= lockup_mark)
                 / avgIf(day_volume, session_date <  lockup_mark) - 1), 1) AS volume_change_pct
FROM
(
    SELECT
        ticker,
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        toFloat64(sum(volume))                               AS day_volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('ARM', 'CART', 'BIRK', 'ALAB', 'RDDT', 'RBRK')
      AND window_start >= toDateTime('2024-01-15 00:00:00')
      AND window_start <  toDateTime('2024-12-15 00:00:00')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
    GROUP BY ticker, session_date
)
WHERE session_date >= lockup_mark - 30
  AND session_date <= lockup_mark + 30
GROUP BY ticker
HAVING countIf(session_date <  lockup_mark) > 0
   AND countIf(session_date >= lockup_mark) > 0
ORDER BY volume_change_pct DESC