march_2020_sessions

SELECT
    toString(d)                                            AS session_date,
    concat(monthName(d), ' ', toString(toDayOfMonth(d)))   AS session_label,
    round((open_px / prev_close_px - 1) * 100, 2)          AS open_vs_prior_close_pct,
    round((low_px / prev_close_px - 1) * 100, 2)           AS low_vs_prior_close_pct,
    round((close_px / prev_close_px - 1) * 100, 2)         AS close_vs_prior_close_pct,
    multiIf((1 - low_px / prev_close_px) * 100 >= 20, 'Level 3 line crossed',
            (1 - low_px / prev_close_px) * 100 >= 13, 'Level 2 line crossed',
            (1 - low_px / prev_close_px) * 100 >= 7,  'Level 1 line crossed',
            '')                                            AS breaker_line
FROM
(
    SELECT
        d,
        open_px,
        low_px,
        close_px,
        lagInFrame(close_px, 1) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_close_px
    FROM
    (
        SELECT
            date                        AS d,
            toFloat64(max(open))        AS open_px,
            toFloat64(min(low))         AS low_px,
            toFloat64(max(close))       AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date BETWEEN toDate('2020-02-24') AND toDate('2020-03-31')
        GROUP BY date
    )
)
WHERE d >= toDate('2020-03-02')
ORDER BY d