RSI-14 in Pure SQL
- RSI
- CASE WHEN
- NULLIF
- window functions
- ROWS BETWEEN
- technical analysis
- signal generation
RSI-14 in Pure SQL - the oscillator on every trading screen 📈
Scene: the momentum desk, 8:55am. The strategist drops a note on your desk: "AAPL has ripped nine sessions straight. Before the open, tell me every day in our sample where RSI-14 crossed into overbought or oversold territory." On a Bloomberg terminal that is one function key. But your firm's tick data lives in the SQL warehouse, not in Bloomberg - so today you rebuild the Relative Strength Index from raw closes, using nothing but window functions.
RSI, invented by J. Welles Wilder Jr. in 1978, answers one question with a single number from 0 to 100: has this thing gone up too much, or down too much, lately? Above 70 the crowd may be euphoric (overbought); below 30, capitulating (oversold).
🧠 The mental model: a tug-of-war scored 0 to 100
Picture bulls and bears in a 14-day tug-of-war. Each up-day the bulls gain rope (a gain); each down-day the bears do (a loss). RSI is just the bulls' share of the total rope pulled over the last 14 days, rescaled to 0-100:
- All 14 days up → avg loss ≈ 0 → RS huge → RSI ≈ 100 (screaming overbought)
- All 14 days down → avg gain ≈ 0 → RS ≈ 0 → RSI ≈ 0 (screaming oversold)
- A fair fight → RSI ≈ 50 (no edge either way)
The formula
RSI = 100 − 100 / (1 + RS), where RS = (Average Gain over 14 days) / (Average Loss over 14 days)
| Term | Definition |
|---|---|
| gain | max(close − prev_close, 0) - the up-move, else 0 |
| loss | max(prev_close − close, 0) - the down-move as a positive number, else 0 |
| avg gain / avg loss | mean of gain / loss over the trailing 14 rows |
The window frame is the whole game
Here is the subtlety that trips people up. `AVG(gain) OVER (PARTITION BY ticker ORDER BY trade_date)` does not give you a 14-day average - with an ORDER BY and no explicit frame, SQL quietly averages everything from the start up to today (an ever-growing window). And with no ORDER BY at all, it averages the entire two-year history - a single lifetime number that hugs 50 and never flags anything.
RSI-14 means exactly 14 rows: today plus the 13 before it. In SQL that is a frame clause:
```sql AVG(gain) OVER ( PARTITION BY ticker ORDER BY trade_date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW -- 13 + today = 14 rows ) ```
| Frame you write | What each row averages | RSI you get |
|---|---|---|
| (no ORDER BY) | the entire history | flat ≈ 50, useless |
| ORDER BY, no ROWS frame | everything up to today (growing) | drifts, wrong |
| `ROWS BETWEEN 13 PRECEDING AND CURRENT ROW` | today + prior 13 days | the real RSI-14 ✅ |
Signal logic
| RSI | Signal | What a trader reads into it |
|---|---|---|
| > 70 | 🔴 Overbought | Stretched up - consider trimming / shorting |
| < 30 | 🟢 Oversold | Stretched down - consider buying / covering |
| 30–70 | ⚪ Neutral | No clear edge |
Your Task
The query is fully wired - LAG for the daily change, the gain/loss `CASE`, the RS-to-RSI math, the signal `CASE`, and a filter that keeps only overbought/oversold days for AAPL. One thing is missing: the 14-day window frame. Right now both `AVG`s span the whole history, so every `rsi_14` sits near 50, the overbought/oversold filter matches nothing, and you get zero rows back. Add the rolling frame to both `AVG(...) OVER (...)` clauses.
Predict before you run: with the placeholder's lifetime average, what single value will every `rsi_14` cluster around - and why does that make the `WHERE rsi_14 > 70 OR rsi_14 < 30` filter return nothing? Once you switch to a true 14-row window, which extreme (🔴 or 🟢) do you expect to dominate for a stock that just ran up nine days straight?