trend_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 >= '2026-03-20'
      AND date <  '2026-07-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 >= '2026-04-01'
      AND (ticker, date) NOT IN
      (
          SELECT ticker, toDate(execution_date)
          FROM global_markets.stocks_splits
          WHERE ticker IN ('SOXX', 'SOXL', 'SOXS')
      )
),
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
    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