Total daily movement versus net movement, June 2026

SELECT
    ticker,
    round(path, 1)                        AS path_length_pct,
    round(net, 1)                         AS net_move_pct,
    round(path / greatest(net, 0.05), 1)  AS path_to_net_ratio
FROM
(
    SELECT
        ticker,
        arraySum(arrayMap((a, b) -> abs(a / b - 1) * 100,
                          arraySlice(px, 2),
                          arraySlice(px, 1, length(px) - 1))) AS path,
        abs(px[-1] / px[1] - 1) * 100                          AS net
    FROM
    (
        SELECT
            ticker,
            arrayMap(t -> t.2, arraySort(t -> t.1, groupArray((session_date, session_close)))) AS px
        FROM
        (
            SELECT
                ticker,
                date                  AS session_date,
                toFloat64(max(close)) AS session_close
            FROM global_markets.stocks_daily_aggs
            WHERE ticker IN ('SPY', 'AAPL', 'MSFT', 'KO')
              AND date >= '2026-06-01'
              AND date <  '2026-07-01'
            GROUP BY ticker, date
        )
        GROUP BY ticker
    )
)
ORDER BY path_to_net_ratio