2,170 answered market questions

WITH
    bars AS (
        SELECT
            date,
            any(toFloat64(close)) AS c,
            max(toFloat64(high))  AS h
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date BETWEEN '2024-07-01' AND '2024-08-08'
        GROUP BY date
    ),
    anchor AS (
        SELECT argMin(c, date) * 1.02 AS lvl
        FROM bars
    )
SELECT
    toString(b.date)                  AS session_date,
    round(b.c, 2)                     AS spy_close,
    round(b.h, 2)                     AS spy_high,
    round(a.lvl, 2)                   AS strike_level,
    round(100 * (b.c / a.lvl - 1), 2) AS close_vs_level_pct,
    round(100 * (max(b.h) OVER (ORDER BY b.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / a.lvl - 1), 2) AS run_max_vs_level_pct
FROM bars AS b
CROSS JOIN anchor AS a
ORDER BY b.date