52-Week Highs, Lows, and Breakout Detection

The 52-week high and low are among the most widely watched price levels in stock markets. They appear on every financial data platform — Yahoo Finance, Bloomberg, CNBC — because they mark significant psychological price points. A stock breaking above its 52-week high often triggers momentum buying; a stock falling to its 52-week low often triggers additional selling or value-buying interest.

This lesson computes these levels in SQL and builds breakout detection — the alert that fires when a stock reaches a new milestone.

Why These Levels Matter

• 52-week high: The highest price in the past year. Breaking through it signals strong momentum and often triggers technical buying. Studies show that stocks making new 52-week highs statistically continue to outperform in the short term.
• 52-week low: The lowest price in the past year. Some value investors consider this a buying opportunity; others see it as a sign of continued weakness.
• Distance from high: If a stock is 15% below its 52-week high, it needs to gain ~18% to recover. This metric helps size position recovery requirements.

Step 1 — Current 52-Week High and Low

A 252-trading-day window (approximately one year of trading days) gives us the 52-week range. ROWS BETWEEN 251 PRECEDING AND CURRENT ROW covers the current day plus the 251 days before it.

For each stock on each date, we compute the rolling highest and lowest adjusted close prices over the past year.

WITH yearly_range AS (
    SELECT
        company_id,
        price_date,
        close_price,
        MAX(close_price) OVER (
            PARTITION BY company_id
            ORDER BY price_date
            ROWS BETWEEN 251 PRECEDING AND CURRENT ROW
        ) AS high_52w,
        MIN(close_price) OVER (
            PARTITION BY company_id
            ORDER BY price_date
            ROWS BETWEEN 251 PRECEDING AND CURRENT ROW
        ) AS low_52w
    FROM stock_prices
)
SELECT
    c.ticker, c.name, c.sector,
    yr.price_date,
    yr.close_price AS current_price,
    ROUND(yr.high_52w, 2) AS high_52w,
    ROUND(yr.low_52w, 2) AS low_52w,
    ROUND((yr.close_price - yr.low_52w) / NULLIF(yr.high_52w - yr.low_52w, 0) * 100, 1) AS position_in_range_pct,
    ROUND((yr.close_price / yr.high_52w - 1) * 100, 2) AS pct_below_52w_high,
    ROUND((yr.close_price / yr.low_52w - 1) * 100, 2) AS pct_above_52w_low
FROM yearly_range yr
JOIN companies c ON c.company_id = yr.company_id
ORDER BY c.ticker, yr.price_date DESC;

What This Returns

position_in_range_pct is particularly useful: 0% means the stock is at its 52-week low, 100% means it's at its 52-week high, 50% means midpoint. A stock at 90%+ of its range is showing strong momentum. A stock at 10%- is near its lows and may be in distress.

pct_below_52w_high shows how far the stock has pulled back from its peak. A value of -20% means the stock is in a 20% drawdown from its annual high.