3,256 answered market questions

SELECT
    toYear(session_date)   AS year,
    countIf(ret_pct <= -2) AS sessions_down_2pct,
    round(min(ret_pct), 2) AS worst_day_pct
FROM
(
    SELECT
        session_date,
        c,
        lagInFrame(c) OVER (ORDER BY session_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_c,
        if(prev_c > 0, round(100 * (c / prev_c - 1), 2), NULL)                                  AS ret_pct
    FROM
    (
        SELECT
            date                   AS session_date,
            toFloat64(max(close))  AS c
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2016-01-01'
          AND date < today()
        GROUP BY date
    )
)
WHERE ret_pct IS NOT NULL
GROUP BY year
ORDER BY sessions_down_2pct DESC, year ASC