skew_by_year

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH rets AS
(
    SELECT
        date,
        log(close_px / prev_px) AS r
    FROM
    (
        SELECT
            date,
            close_px,
            lagInFrame(close_px, 1) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_px
        FROM
        (
            SELECT
                date,
                toFloat64(any(close)) AS close_px
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'SPY'
              AND date BETWEEN toDate('2015-12-31') AND toDate('2025-12-31')
            GROUP BY date
        )
    )
    WHERE prev_px > 0
      AND toYear(date) >= 2016
)
SELECT
    annee,
    seances_count,
    skewness,
    pire_seance_pct,
    meilleure_seance_pct
FROM
(
    SELECT
        toString(toYear(date))  AS annee,
        toYear(date)            AS sort_key,
        count()                 AS seances_count,
        round(skewSamp(r), 2)   AS skewness,
        round(min(r) * 100, 2)  AS pire_seance_pct,
        round(max(r) * 100, 2)  AS meilleure_seance_pct
    FROM rets
    GROUP BY annee, sort_key
    UNION ALL
    SELECT
        '2016 à 2025'           AS annee,
        9999                    AS sort_key,
        count()                 AS seances_count,
        round(skewSamp(r), 2)   AS skewness,
        round(min(r) * 100, 2)  AS pire_seance_pct,
        round(max(r) * 100, 2)  AS meilleure_seance_pct
    FROM rets
)
ORDER BY sort_key