2,170 answered market questions

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMax(close, window_start) AS close,
           sum(toFloat64(volume)) AS vol
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ((ticker = 'FB'   AND window_start >= toDateTime('2012-11-12 00:00:00') AND window_start < toDateTime('2012-11-15 00:00:00'))
        OR (ticker = 'TWTR' AND window_start >= toDateTime('2014-05-04 00:00:00') AND window_start < toDateTime('2014-05-07 00:00:00'))
        OR (ticker = 'SNAP' AND window_start >= toDateTime('2017-07-27 00:00:00') AND window_start < toDateTime('2017-08-01 00:00:00'))
        OR (ticker = 'LYFT' AND window_start >= toDateTime('2019-08-15 00:00:00') AND window_start < toDateTime('2019-08-20 00:00:00'))
        OR (ticker = 'UBER' AND window_start >= toDateTime('2019-11-04 00:00:00') AND window_start < toDateTime('2019-11-07 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 ticker, d
),
packed AS (
    SELECT ticker, arraySort(x -> x.1, groupArray((d, close, vol))) AS rows
    FROM daily
    GROUP BY ticker
)
SELECT ticker,
       toString(rows[-1].1) AS expiry_session,
       round(rows[-2].2, 2) AS prior_close,
       round(rows[-1].2, 2) AS expiry_close,
       round((rows[-1].2 / rows[-2].2 - 1) * 100, 1) AS expiry_day_change_pct,
       round(rows[-1].3 / 1e6, 1) AS expiry_shares_m,
       round(rows[-1].3 / rows[-2].3, 1) AS volume_multiple
FROM packed
ORDER BY expiry_session