dragonfly_context

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
),
shaped AS
(
    SELECT
        ticker,
        date,
        c,
        h - l              AS rng,
        abs(c - o)         AS body,
        h - greatest(o, c) AS upper_wick,
        least(o, c) - l    AS lower_wick,
        0.01 * o           AS min_range,
        lagInFrame(c, 5)   OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)  AS c_prev5,
        leadInFrame(c, 5)  OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS c_fwd5,
        leadInFrame(c, 20) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS c_fwd20
    FROM bars
),
tagged AS
(
    SELECT
        ticker,
        date,
        c,
        c_prev5,
        c_fwd5,
        c_fwd20,
        multiIf(
            rng >= min_range AND body <= 0.10 * rng AND upper_wick <= 0.10 * rng, 'dragonfly',
            rng >= min_range AND body <= 0.10 * rng AND lower_wick <= 0.10 * rng, 'gravestone',
            'other_days') AS pattern
    FROM shaped
),
ctx AS
(
    SELECT
        ticker,
        date,
        c,
        c_fwd5,
        c_fwd20,
        concat(pattern, if(c < c_prev5, '_after_5d_decline', '_mid_range')) AS context
    FROM tagged
    WHERE pattern != 'gravestone'
      AND c_prev5 > 0
      AND c_fwd5  > 0
      AND c_fwd20 > 0
)
SELECT
    context,
    count()                 AS sample_count,
    if(count() >= 10000,
       concat(toString(round(count() / 1000)), ' ஆயிரம்'),
       toString(count()))   AS sample_size,
    round(100 * (quantileDeterministic(0.5)(c_fwd5  / c, cityHash64(concat(ticker, toString(date)))) - 1), 3) AS median_5d_pct,
    round(100 * (quantileDeterministic(0.5)(c_fwd20 / c, cityHash64(concat(ticker, toString(date)))) - 1), 3) AS median_20d_pct
FROM ctx
GROUP BY context
ORDER BY context