Anchored at each name's own lowest close of the past twelve months

WITH
    bars AS (
        SELECT
            ticker,
            date,
            toFloat64(vwap)   AS px,
            toFloat64(volume) AS vol,
            toFloat64(close)  AS c
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'SPY')
          AND date >= '2025-07-01'
          AND date <= '2026-06-30'
    ),
    lows AS (
        SELECT
            ticker,
            argMin(date, c) AS low_date
        FROM bars
        GROUP BY ticker
    )
SELECT
    b.ticker                                AS symbol,
    formatDateTime(l.low_date, '%b %e, %Y') AS anchored_at,
    round(sumIf(b.px * b.vol, b.date >= l.low_date) / sumIf(b.vol, b.date >= l.low_date), 2) AS anchored_vwap,
    round(argMax(b.c, b.date), 2)           AS last_close,
    round(100 * (argMax(b.c, b.date)
        / (sumIf(b.px * b.vol, b.date >= l.low_date) / sumIf(b.vol, b.date >= l.low_date)) - 1), 1) AS close_vs_avwap_pct
FROM bars AS b
INNER JOIN lows AS l ON b.ticker = l.ticker
GROUP BY b.ticker, l.low_date
ORDER BY close_vs_avwap_pct DESC