inversions

SELECT
    formatDateTime(min(month), '%b %Y')   AS began,
    formatDateTime(max(month), '%b %Y')   AS ended,
    toString(min(month))                  AS start_date,
    count()                               AS months_inverted,
    toInt32(round(min(spread_bps)))       AS deepest_month_bps,
    toInt32(round(avg(spread_bps)))       AS average_depth_bps
FROM
(
    SELECT
        month,
        spread_bps,
        sum(if(spread_bps < 0, 0, 1)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_id
    FROM
    (
        SELECT
            toStartOfMonth(date)                                              AS month,
            avg(toFloat64(yield_10_year) - toFloat64(yield_3_month)) * 100    AS spread_bps
        FROM global_markets.treasury_yields
        WHERE date >= toDate('1985-01-01')
          AND yield_3_month > 0
          AND yield_10_year > 0
        GROUP BY month
    )
)
WHERE spread_bps < 0
GROUP BY run_id
ORDER BY start_date