2,170 answered market questions

WITH daily AS (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        argMaxIf(toFloat64(close), window_start, ticker = 'SPY' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS spy_close_raw,
        round(argMaxIf(toFloat64(close), window_start, ticker = 'C' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 2) AS citi_close,
        round(toFloat64(sumIf(volume, ticker = 'SPY')) / 1e6, 1) AS spy_shares_m
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'C')
      AND window_start >= toDateTime('2009-03-06 04:00:00') AND window_start < toDateTime('2009-03-14 00:00:00')
    GROUP BY et_date
)
SELECT
    toString(d.et_date) AS session,
    round(d.spy_close_raw, 2) AS spy_close,
    round((d.spy_close_raw / d.prev_close - 1) * 100, 1) AS spy_change_pct,
    d.spy_shares_m,
    d.citi_close,
    t.ten_year_pct
FROM (
    SELECT *, lagInFrame(spy_close_raw) OVER (ORDER BY et_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM daily
) AS d
LEFT JOIN (
    SELECT date, round(toFloat64(yield_10_year), 2) AS ten_year_pct
    FROM global_markets.treasury_yields
    WHERE date BETWEEN '2009-03-09' AND '2009-03-13'
) AS t ON d.et_date = t.date
WHERE d.et_date >= toDate('2009-03-09')
ORDER BY d.et_date