bos_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
px AS
(
    SELECT
        date,
        toFloat64(high)  AS h,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2025-01-02'
      AND date <  '2025-08-01'
),
flagged AS
(
    SELECT
        date,
        h,
        c,
        max(h) OVER w_prev  AS prev_high,
        max(h) OVER w_next  AS next_high,
        count() OVER w_prev AS bars_before,
        count() OVER w_next AS bars_after
    FROM px
    WINDOW
        w_prev AS (ORDER BY date ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING),
        w_next AS (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING)
),
pivot AS
(
    SELECT
        date AS pivot_date,
        h    AS level
    FROM flagged
    WHERE bars_before = 3
      AND bars_after = 3
      AND h > prev_high
      AND h > next_high
      AND date <= '2025-04-30'
    ORDER BY date DESC
    LIMIT 1
)
SELECT
    toString(f.date)                         AS session_date,
    round(f.c, 2)                            AS close,
    round(p.level, 2)                        AS swing_high_level,
    formatDateTime(p.pivot_date, '%d/%m/%Y') AS pivot_label
FROM flagged AS f
CROSS JOIN pivot AS p
WHERE f.date >= p.pivot_date - 20
  AND f.date <= p.pivot_date + 60
ORDER BY f.date