flat_months

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
)
SELECT
    concat(toString(toYear(m)), '年', toString(toMonth(m)), '月') AS label,
    round(soxx * 100, 1)                                        AS index_pct,
    round(soxl * 100, 1)                                        AS soxl_pct,
    round(soxs * 100, 1)                                        AS soxs_pct,
    round((soxl + soxs) * 100, 1)                               AS pair_pct
FROM monthly
WHERE abs(soxx) < 0.025
ORDER BY m