3,256 answered market questions

SELECT
    label,
    round(sigma * 100, 3)                AS sigma_pct,
    round(sigma / avg(sigma) OVER (), 2) AS vs_all_days_ratio,
    sessions
FROM
(
    SELECT
        formatDateTime(session_date, '%a') AS label,
        min(toDayOfWeek(session_date))     AS dow,
        stddevPop(daily_return)            AS sigma,
        count()                            AS sessions
    FROM
    (
        SELECT
            session_date,
            close_px / lagInFrame(close_px) OVER (ORDER BY session_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS daily_return
        FROM
        (
            SELECT
                date                  AS session_date,
                toFloat64(max(close)) AS close_px
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'SPY'
              AND date >= today() - 1100
              AND date <  today() - 1
            GROUP BY session_date
        )
    )
    WHERE isFinite(daily_return)
    GROUP BY label
)
ORDER BY dow