Orari di apertura e chiusura del mercato azionario

WITH
(
    SELECT max(d)
    FROM
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            countIf((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) AS rth_bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 21
          AND window_start < today() - 2
        GROUP BY d
        HAVING rth_bars = 390
    )
) AS session_day
SELECT
    toString(session_day)                     AS session_date,
    formatDateTime(session_day, '%W')         AS weekday,
    b.regular_session_bars                    AS regular_session_bars,
    b.first_bar_minute_of_day                 AS first_bar_minute_of_day,
    b.last_bar_minute_of_day                  AS last_bar_minute_of_day,
    b.first_bar_et                            AS first_bar_et,
    b.last_bar_et                             AS last_bar_et,
    t.total_shares_m                          AS total_shares_m,
    t.premarket_pct                           AS premarket_pct,
    t.regular_pct                             AS regular_pct,
    t.post_close_30min_pct                    AS post_close_30min_pct,
    t.evening_pct                             AS evening_pct,
    t.first_30min_pct                         AS first_30min_pct,
    t.last_30min_pct                          AS last_30min_pct,
    t.midday_half_hour_pct                    AS midday_half_hour_pct
FROM
(
    SELECT
        countIf(et_min >= 570 AND et_min < 960)   AS regular_session_bars,
        min(et_min)                               AS first_bar_minute_of_day,
        max(et_min)                               AS last_bar_minute_of_day,
        formatDateTime(min(et_ts), '%H:%i')       AS first_bar_et,
        formatDateTime(max(et_ts), '%H:%i')       AS last_bar_et
    FROM
    (
        SELECT
            toTimeZone(window_start, 'America/New_York') AS et_ts,
            toHour(toTimeZone(window_start, 'America/New_York')) * 60
            + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_min
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND toDate(toTimeZone(window_start, 'America/New_York')) = session_day
    )
) AS b
CROSS JOIN
(
    SELECT
        round(sum(shares) / 1e6, 1)                                                                   AS total_shares_m,
        round(100.0 * sumIf(shares, NOT is_close AND et_min < 570) / sum(shares), 1)                  AS premarket_pct,
        round(100.0 * sumIf(shares, is_close OR (et_min >= 570 AND et_min < 960)) / sum(shares), 1)   AS regular_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 960 AND et_min < 990) / sum(shares), 1) AS post_close_30min_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 990) / sum(shares), 1)                 AS evening_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 570 AND et_min < 600) / sum(shares), 1) AS first_30min_pct,
        round(100.0 * sumIf(shares, is_close OR (et_min >= 930 AND et_min < 960)) / sum(shares), 1)   AS last_30min_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 750 AND et_min < 780) / sum(shares), 1) AS midday_half_hour_pct
    FROM
    (
        SELECT
            toFloat64(size)    AS shares,
            has(conditions, 8) AS is_close,
            toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60
            + toMinute(toTimeZone(sip_timestamp, 'America/New_York')) AS et_min
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPY'
          AND sip_timestamp >= toDateTime(session_day, 'America/New_York')
          AND sip_timestamp <  toDateTime(session_day + 1, 'America/New_York')
          AND NOT hasAny(conditions, [15, 16, 38])
    )
) AS t

WITH
(
    SELECT max(d)
    FROM
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            countIf((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) AS rth_bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 21
          AND window_start < today() - 2
        GROUP BY d
        HAVING rth_bars = 390
    )
) AS session_day
SELECT
    formatDateTime(toStartOfInterval(if(has(conditions, 8),
                                         toTimeZone(sip_timestamp, 'America/New_York') - INTERVAL 10 MINUTE,
                                         toTimeZone(sip_timestamp, 'America/New_York')),
                                      INTERVAL 30 MINUTE), '%H:%i') AS et_half_hour,
    round(sum(toFloat64(size)) / 1e6, 2) AS shares_m
FROM global_markets.stocks_trades
WHERE ticker = 'SPY'
  AND sip_timestamp >= toDateTime(session_day, 'America/New_York')
  AND sip_timestamp <  toDateTime(session_day + 1, 'America/New_York')
  AND NOT hasAny(conditions, [15, 16, 38])
GROUP BY et_half_hour
ORDER BY et_half_hour

WITH
(
    SELECT max(d)
    FROM
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            countIf((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) AS rth_bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 21
          AND window_start < today() - 2
        GROUP BY d
        HAVING rth_bars = 390
    )
) AS session_day
SELECT
    if(startsWith(ticker, 'O:SPY'), 'SPY options (ETF)', 'AAPL options (single name)') AS contract,
    toString(session_day)                                                              AS session_date,
    countIf(sip_timestamp >= toDateTime(concat(toString(session_day), ' 15:45:00'), 'America/New_York')
        AND sip_timestamp <  toDateTime(concat(toString(session_day), ' 16:00:00'), 'America/New_York')) AS trades_345_to_400,
    countIf(sip_timestamp >= toDateTime(concat(toString(session_day), ' 16:00:00'), 'America/New_York')
        AND sip_timestamp <  toDateTime(concat(toString(session_day), ' 16:15:00'), 'America/New_York')) AS trades_400_to_415,
    formatDateTime(toTimeZone(max(sip_timestamp), 'America/New_York'), '%H:%i') AS last_trade_et,
    toHour(toTimeZone(max(sip_timestamp), 'America/New_York')) * 60
    + toMinute(toTimeZone(max(sip_timestamp), 'America/New_York'))              AS last_trade_minute_of_day
FROM global_markets.options_trades
WHERE (startsWith(ticker, 'O:SPY') OR startsWith(ticker, 'O:AAPL'))
  AND match(ticker, '^O:(SPY|AAPL)[0-9]{6}[CP][0-9]{8}$')
  AND sip_timestamp >= toDateTime(concat(toString(session_day), ' 15:45:00'), 'America/New_York')
  AND sip_timestamp <  toDateTime(concat(toString(session_day), ' 23:00:00'), 'America/New_York')
GROUP BY contract
ORDER BY contract

SELECT
    toString(date)                       AS holiday_date,
    formatDateTime(date, '%a')           AS weekday,
    any(name)                            AS holiday,
    any(status)                          AS day_status,
    any(if(status = 'early-close',
           formatDateTime(toTimeZone(close, 'America/New_York'), '%I:%i %p'),
           ''))                          AS early_close_et,
    dateDiff('day', today(), date)       AS days_away
FROM global_markets.stocks_market_holidays
WHERE date >= today()
GROUP BY date
ORDER BY date

WITH
(
    SELECT max(d)
    FROM
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            countIf((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) AS rth_bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 400
          AND window_start < today() - 2
        GROUP BY d
        HAVING rth_bars >= 150 AND rth_bars <= 240
    )
) AS half_day
SELECT
    toString(half_day)                          AS session_date,
    formatDateTime(half_day, '%W')              AS weekday,
    b.morning_session_bars                      AS morning_session_bars,
    b.last_bar_et                               AS last_bar_et,
    b.last_bar_minute_of_day                    AS last_bar_minute_of_day,
    t.total_shares_m                            AS total_shares_m,
    t.premarket_pct                             AS premarket_pct,
    t.regular_pct                               AS regular_pct,
    t.post_close_30min_pct                      AS post_close_30min_pct,
    t.evening_pct                               AS evening_pct,
    t.first_30min_pct                           AS first_30min_pct,
    t.last_30min_pct                            AS last_30min_pct,
    round(t.last_30min_pct - t.first_30min_pct, 1) AS close_minus_open_pct
FROM
(
    SELECT
        countIf(et_min >= 570 AND et_min < 780)     AS morning_session_bars,
        formatDateTime(max(et_ts), '%H:%i')         AS last_bar_et,
        max(et_min)                                 AS last_bar_minute_of_day
    FROM
    (
        SELECT
            toTimeZone(window_start, 'America/New_York') AS et_ts,
            toHour(toTimeZone(window_start, 'America/New_York')) * 60
            + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_min
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 400
          AND toDate(toTimeZone(window_start, 'America/New_York')) = half_day
    )
) AS b
CROSS JOIN
(
    SELECT
        round(sum(shares) / 1e6, 1)                                                                   AS total_shares_m,
        round(100.0 * sumIf(shares, NOT is_close AND et_min < 570) / sum(shares), 1)                  AS premarket_pct,
        round(100.0 * sumIf(shares, is_close OR (et_min >= 570 AND et_min < 780)) / sum(shares), 1)   AS regular_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 780 AND et_min < 810) / sum(shares), 1) AS post_close_30min_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 810) / sum(shares), 1)                 AS evening_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 570 AND et_min < 600) / sum(shares), 1) AS first_30min_pct,
        round(100.0 * sumIf(shares, is_close OR (et_min >= 750 AND et_min < 780)) / sum(shares), 1)   AS last_30min_pct
    FROM
    (
        SELECT
            toFloat64(size)    AS shares,
            has(conditions, 8) AS is_close,
            toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60
            + toMinute(toTimeZone(sip_timestamp, 'America/New_York')) AS et_min
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPY'
          AND sip_timestamp >= toDateTime(half_day, 'America/New_York')
          AND sip_timestamp <  toDateTime(half_day + 1, 'America/New_York')
          AND NOT hasAny(conditions, [15, 16, 38])
    )
) AS t

WITH
(
    SELECT max(d)
    FROM
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            countIf((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) AS rth_bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 400
          AND window_start < today() - 2
        GROUP BY d
        HAVING rth_bars >= 150 AND rth_bars <= 240
    )
) AS half_day
SELECT
    formatDateTime(toStartOfInterval(if(has(conditions, 8),
                                         toTimeZone(sip_timestamp, 'America/New_York') - INTERVAL 10 MINUTE,
                                         toTimeZone(sip_timestamp, 'America/New_York')),
                                      INTERVAL 30 MINUTE), '%H:%i') AS et_half_hour,
    round(sum(toFloat64(size)) / 1e6, 2) AS shares_m
FROM global_markets.stocks_trades
WHERE ticker = 'SPY'
  AND sip_timestamp >= toDateTime(half_day, 'America/New_York')
  AND sip_timestamp <  toDateTime(half_day + 1, 'America/New_York')
  AND NOT hasAny(conditions, [15, 16, 38])
GROUP BY et_half_hour
ORDER BY et_half_hour

SELECT
    count()                              AS trading_days,
    countIf(rth_bars >= 380)             AS full_length_sessions,
    countIf(rth_bars <= 240)             AS shortened_sessions,
    countIf(rth_bars > 240 AND rth_bars < 380) AS irregular_sessions,
    countIf(is_weekend)                  AS weekend_sessions,
    toString(min(d))                     AS first_session,
    toString(max(d))                     AS last_session
FROM
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS d,
        toDayOfWeek(toDate(toTimeZone(window_start, 'America/New_York'))) >= 6 AS is_weekend,
        countIf((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) AS rth_bars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= today() - 370
      AND window_start < today() - 2
    GROUP BY d, is_weekend
    HAVING rth_bars > 0
)