pattern_census

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 bars AS
(
    SELECT
        ticker,
        date,
        toFloat64(open)  AS o,
        toFloat64(high)  AS h,
        toFloat64(low)   AS l,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','XOM','JNJ','KO','PG','WMT','HD','UNH','CVX','MRK','CSCO','PEP','SPY','QQQ')
      AND date >= '2006-01-01'
      AND date <  '2026-10-01'
      AND high > low
      AND low > 0
)
SELECT
    toYear(date)                                      AS year,
    countIf((h - l) >= 0.01 * o
            AND abs(c - o)         <= 0.10 * (h - l)
            AND (h - greatest(o, c)) <= 0.10 * (h - l)) AS dragonfly_days,
    countIf((h - l) >= 0.01 * o
            AND abs(c - o)         <= 0.10 * (h - l)
            AND (least(o, c) - l)  <= 0.10 * (h - l)) AS gravestone_days,
    count()                                           AS session_count
FROM bars
GROUP BY year
ORDER BY year