2,170 answered market questions

WITH
    daily AS (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
            argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS rth_close,
            maxIf(toFloat64(high), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS rth_high
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA'
          AND window_start >= toDateTime('2023-05-24 00:00:00') AND window_start < toDateTime('2023-06-20 00:00:00')
        GROUP BY et_date
    ),
    firsts AS (
        SELECT
            minIf(et_date, rth_high >= 404.86) AS ft,
            minIf(et_date, rth_close >= 404.86) AS fc
        FROM daily
    ),
    (SELECT any(rth_close) FROM daily WHERE et_date = toDate('2023-05-24')) AS pre_close,
    (
        SELECT argMinIf(toFloat64(open), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA'
          AND window_start >= toDateTime('2023-05-25 04:00:00') AND window_start < toDateTime('2023-05-26 00:00:00')
    ) AS may25_open
SELECT
    round((may25_open - pre_close) * 2.47, 0) AS overnight_cap_added_bn,
    toString(any(f.ft)) AS first_touch_session,
    round(anyIf(d.rth_high, d.et_date = f.ft), 2) AS first_touch_high,
    round(anyIf(d.rth_close, d.et_date = f.ft), 2) AS first_touch_close,
    countIf(d.et_date > toDate('2023-05-24') AND d.et_date <= f.ft) AS sessions_to_first_touch,
    toString(any(f.fc)) AS first_close_session,
    round(anyIf(d.rth_close, d.et_date = f.fc), 2) AS first_close_usd,
    countIf(d.et_date > toDate('2023-05-24') AND d.et_date <= f.fc) AS sessions_to_first_close,
    round((anyIf(d.rth_close, d.et_date = f.fc) / pre_close - 1) * 100, 1) AS first_close_cum_pct
FROM daily AS d
CROSS JOIN firsts AS f