2,401 answered market questions

WITH
daily AS
(
    SELECT
        date,
        dateDiff('day', toDate('1970-01-01'), date)   AS dnum,
        ifNull(toFloat64(max(yield_2_year)), 0.)      AS y2,
        ifNull(toFloat64(max(yield_10_year)), 0.)     AS y10,
        y10 - y2                                      AS spread
    FROM global_markets.treasury_yields
    WHERE yield_2_year > 0 AND yield_10_year > 0
    GROUP BY date
),
scored AS
(
    SELECT
        date, dnum, y2, y10, spread,
        count()         OVER (ORDER BY date ROWS BETWEEN 60 PRECEDING AND 1 PRECEDING) AS rows_prior,
        sum(spread < 0) OVER (ORDER BY date ROWS BETWEEN 60 PRECEDING AND 1 PRECEDING) AS neg_prior,
        sum(spread > 0) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS pos_next
    FROM daily
),
candidates AS
(
    SELECT
        date, dnum, y2, y10, spread,
        lagInFrame(dnum, 1, toInt64(-100000)) OVER (ORDER BY dnum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_dnum
    FROM scored
    WHERE spread > 0 AND rows_prior = 60 AND neg_prior >= 35 AND pos_next >= 19
),
episodes AS
(
    SELECT date AS cross_date, y2, y10, spread
    FROM candidates
    WHERE dnum - prev_dnum > 180
      AND date <= today() - 380
)
SELECT
    episode,
    crossed_on,
    y2_91d_before,
    y2_at_cross,
    y10_91d_before,
    y10_at_cross,
    y2_change_bp,
    y10_change_bp,
    multiIf(
        y2_change_bp < 0 AND abs(y2_change_bp) >= abs(y10_change_bp), 'bull steepener (2-year fell most)',
        y10_change_bp > 0 AND abs(y10_change_bp) > abs(y2_change_bp), 'bear steepener (10-year rose most)',
        'mixed'
    ) AS steepener_type
FROM
(
    SELECT
        formatDateTime(e.cross_date, '%b %Y')                                       AS episode,
        toString(toDate(e.cross_date))                                              AS crossed_on,
        round(argMinIf(d.y2, d.date, d.date >= addDays(e.cross_date, -91)), 2)      AS y2_91d_before,
        round(any(e.y2), 2)                                                         AS y2_at_cross,
        round(argMinIf(d.y10, d.date, d.date >= addDays(e.cross_date, -91)), 2)     AS y10_91d_before,
        round(any(e.y10), 2)                                                        AS y10_at_cross,
        round((any(e.y2)  - argMinIf(d.y2,  d.date, d.date >= addDays(e.cross_date, -91))) * 100) AS y2_change_bp,
        round((any(e.y10) - argMinIf(d.y10, d.date, d.date >= addDays(e.cross_date, -91))) * 100) AS y10_change_bp
    FROM episodes AS e
    CROSS JOIN daily AS d
    WHERE d.date BETWEEN addDays(e.cross_date, -100) AND e.cross_date
    GROUP BY e.cross_date
)
ORDER BY crossed_on