decay_trace

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
daily AS
(
    SELECT
        ticker,
        date,
        anyLast(toFloat64(close)) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SOXX', 'SOXL', 'SOXS')
      AND date >= '2021-12-15'
      AND date <  '2026-09-01'
    GROUP BY ticker, date
),
lagged AS
(
    SELECT
        ticker,
        date,
        c,
        lagInFrame(c, 1) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_c
    FROM daily
),
rets AS
(
    SELECT
        ticker,
        date,
        c / prev_c - 1 AS r
    FROM lagged
    WHERE prev_c > 0
      AND abs(c / prev_c - 1) < 0.75
      AND date >= '2022-01-01'
      AND (ticker, date) NOT IN
      (
          SELECT ticker, toDate(execution_date)
          FROM global_markets.stocks_splits
          WHERE ticker IN ('SOXX', 'SOXL', 'SOXS')
      )
),
monthly AS
(
    SELECT
        toStartOfMonth(date)                        AS m,
        exp(sumIf(log(1 + r), ticker = 'SOXX')) - 1 AS soxx,
        exp(sumIf(log(1 + r), ticker = 'SOXL')) - 1 AS soxl,
        exp(sumIf(log(1 + r), ticker = 'SOXS')) - 1 AS soxs
    FROM rets
    GROUP BY m
    HAVING countIf(ticker = 'SOXX') >= 15
       AND countIf(ticker = 'SOXL') >= 15
       AND countIf(ticker = 'SOXS') >= 15
),
picked AS
(
    SELECT argMin(m, soxl + soxs) AS pm
    FROM monthly
    WHERE abs(soxx) < 0.025
),
by_day AS
(
    SELECT
        date,
        anyIf(r, ticker = 'SOXX') AS r_index,
        anyIf(r, ticker = 'SOXL') AS r_soxl,
        anyIf(r, ticker = 'SOXS') AS r_soxs
    FROM rets
    WHERE toStartOfMonth(date) IN (SELECT pm FROM picked)
    GROUP BY date
)
SELECT
    toString(date) AS session_date,
    concat(toString(toYear(date)), '年', toString(toMonth(date)), '月', toString(toDayOfMonth(date)), '日') AS day_label,
    round(100 * exp(sum(log(1 + r_index)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)), 1) AS index_rebased,
    round(100 * exp(sum(log(1 + r_soxl)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)), 1)  AS soxl_rebased,
    round(100 * exp(sum(log(1 + r_soxs)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)), 1)  AS soxs_rebased,
    round(100 * exp(sum(log(1 + r_soxl)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
        + 100 * exp(sum(log(1 + r_soxs)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)), 1)  AS pair_rebased
FROM by_day
ORDER BY date