What the session's contracts were made of: options volume by days to expiry

WITH tape AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           sum(toFloat64(volume)) AS vol
    FROM global_markets.options_minute_aggs
    WHERE window_start >= toDateTime(today() - 45, 'America/New_York')
    GROUP BY d
),
ranked AS (
    SELECT d, vol, row_number() OVER (ORDER BY d DESC) AS raw_rn
    FROM tape
),
cal AS (
    SELECT d, vol, rn, sum(if(rn BETWEEN 2 AND 21, 1, 0)) OVER () AS baseline_sessions
    FROM (
        SELECT d, vol, row_number() OVER (ORDER BY d DESC) AS rn
        FROM ranked
        WHERE vol >= 0.75 * (SELECT quantileExact(0.5)(vol) FROM ranked WHERE raw_rn > 1)
    )
),
bars AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           toFloat64(volume) AS v,
           toDate(concat('20', substring(ticker, length(ticker) - 14, 2), '-',
                         substring(ticker, length(ticker) - 12, 2), '-',
                         substring(ticker, length(ticker) - 10, 2))) AS expiry
    FROM global_markets.options_minute_aggs
    WHERE window_start >= toDateTime(today() - 12, 'America/New_York')
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM cal WHERE rn = 1)
),
scored AS (
    SELECT multiIf(expiry - d <= 0, 1, expiry - d <= 7, 2, expiry - d <= 30, 3,
                   expiry - d <= 90, 4, expiry - d <= 365, 5, 6) AS bk,
           v, d
    FROM bars
)
SELECT arrayElement(['same day', '1 to 7 days', '8 to 30 days', '31 to 90 days', '91 to 365 days', 'over a year'], bk) AS dte_bucket,
       round(sum(v) / 1e6, 2) AS contracts_m,
       round(100.0 * sum(v) / sum(sum(v)) OVER (), 1) AS pct_of_volume,
       toYYYYMMDD(max(d)) AS session_id
FROM scored
GROUP BY bk
ORDER BY bk ASC