Moving Averages and Trend Analysis

Moving averages are the most widely used tools in technical analysis. They smooth out short-term price fluctuations to reveal the underlying trend direction. Traders use them to determine whether a stock is in an uptrend or downtrend, and to generate buy and sell signals through crossovers.

This lesson implements multiple moving average types in SQL and builds trend analysis systems around them.

What Is a Moving Average?

A Simple Moving Average (SMA) is the arithmetic mean of the last N closing prices. A 20-day SMA averages the 20 most recent closing prices. As each new day is added, the oldest day drops off — the window "moves" forward in time.

A 20-day SMA represents roughly one month of trading (markets trade ~252 days per year). Common periods:

SMA-5: 1 trading week — very responsive to short-term moves
SMA-20: ~1 month — medium-term trend
SMA-50: ~2.5 months — medium-to-long term trend
SMA-200: ~10 months — long-term bull/bear market trend

When the current price is above the SMA, the stock is in an uptrend for that period. When it crosses below, that's a bearish signal.

Step 1 — Simple Moving Averages (5, 20, 50-Day)

The SQL implementation uses ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW to define the window. ROWS BETWEEN 19 PRECEDING AND CURRENT ROW covers 20 rows (the current row plus 19 before it).

We compute all three SMAs in a single pass over the data. The PARTITION BY company_id ORDER BY price_date ensures each stock's moving averages only use that stock's own price history.

WITH moving_avgs AS (
    SELECT
        company_id,
        price_date,
        close_price,
                ROUND(AVG(close_price) OVER (
            PARTITION BY company_id ORDER BY price_date
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ), 2) AS sma_5,
        ROUND(AVG(close_price) OVER (
            PARTITION BY company_id ORDER BY price_date
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ), 2) AS sma_20,
        ROUND(AVG(close_price) OVER (
            PARTITION BY company_id ORDER BY price_date
            ROWS BETWEEN 49 PRECEDING AND CURRENT ROW
        ), 2) AS sma_50
    FROM stock_prices
)
SELECT
    c.ticker,
    ma.price_date,
    ma.close_price,
    ma.sma_5,
    ma.sma_20,
    ma.sma_50,
    CASE
        WHEN ma.close_price > ma.sma_20 THEN 'Above 20-day MA (bullish)'
        ELSE 'Below 20-day MA (bearish)'
    END AS trend_signal
FROM moving_avgs ma
JOIN companies c ON c.company_id = ma.company_id
ORDER BY c.ticker, ma.price_date DESC;

What This Returns

For each stock on each day: the raw price and three moving averages at different time horizons. The trend_signal column provides a simple binary classification — above the 20-day MA means the stock is in a short-to-medium term uptrend.

The early rows for each stock will have fewer data points than requested (e.g., the SMA-50 for the 30th trading day will only average 30 values). PostgreSQL handles this correctly — it averages whatever rows are available within the frame.

Purchase this course to unlock the full lesson.

← Previous Next →