spike_2020

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 splits AS
(
    SELECT
        groupArray(execution_date) AS split_dates,
        groupArray(price_factor)   AS price_factors
    FROM
    (
        SELECT
            execution_date,
            toFloat64(any(split_from)) / toFloat64(any(split_to)) AS price_factor
        FROM global_markets.stocks_splits
        WHERE ticker = 'VXX'
        GROUP BY execution_date
    )
),
daily AS
(
    SELECT
        a.date AS date,
        toFloat64(a.close) * arrayProduct(arrayMap((d, f) -> if(d > a.date, f, 1.0), s.split_dates, s.price_factors)) AS adj_close
    FROM global_markets.stocks_daily_aggs AS a
    CROSS JOIN splits AS s
    WHERE a.ticker = 'VXX'
      AND a.date >= toDate('2020-02-14')
      AND a.date <  toDate('2021-03-01')
),
weekly AS
(
    SELECT
        toMonday(date)          AS week_start,
        argMax(adj_close, date) AS week_close,
        max(adj_close)          AS week_high
    FROM daily
    GROUP BY week_start
)
SELECT
    toString(week_start)                       AS week,
    formatDateTime(week_start, '%b %e, %Y')    AS week_label,
    round(100 * (week_close / first_value(week_close) OVER (ORDER BY week_start) - 1), 1) AS gain_pct,
    round(100 * (max(week_high) OVER (ORDER BY week_start) / first_value(week_close) OVER (ORDER BY week_start) - 1), 1) AS peak_gain_pct
FROM weekly
ORDER BY week_start