Problem
We want to compare two moving averages in SQL. We’ll use window functions to do this, as it is easier write and manage the query using them.
Background
Moving averages are a commonly used tool in financial analysis that smooths out the price data by averaging the closing prices of a stock over a specified time.
By comparing two moving averages we can find trends in data to construct buy and sell signals. This is something commonly done in financial markets. For our example we will calculate and compare a 7-day moving average versus a 14-day moving average for the Tesla stock price over the past 60 days.
We’ll us the StockPrice table found in the WinCode database for this example.
Note: The tables and data for this example are found located within the EssentialSQL\WinCode GitHub Repo
Solution
Before we get too far into the solution, lets first talk about how we will compute the moving average. In the simplest terms we use the AVG function. Here is an example computing a 7-day moving average.
Related: Watch the Video!!
select TradeDate, ClosingPrice,
avg(ClosingPrice) over(order by StockSymbol, TradeDate
rows between 6 preceding and current row) MovingAverage
from StockPrice
It works well enough but does have an issue. You see it computing an average value for periods 1-6.
For this time, we should be returning null, as there aren’t enough data point to calculate the true moving average.
To remedy this, we can use COUNT() and compare this to ensure we calculate the moving average from period 7 forward.
select TradeDate, ClosingPrice,
case when count(*) over wFast > 6 then avg(ClosingPrice) over wFast else null end MovingAverage
from StockPrice
window wFast as (order by StockSymbol, TradeDate rows between 6 preceding and current row)
Also notice that there is now a WINDOW clause. This helps as the window definition for COUNT and AVG are the same. Code consistency and code maintainability are our friends!
Now we can put together the final solution. There are several things to notice here. First, we’re now calculating two moving averages. Also, since we may want to change moving average lengths as some point, I renamed the columns and windows to reflect that one moving average changes faster than the other.
select TradeDate, ClosingPrice,
case when count(*) over wFast > 6 then avg(ClosingPrice) over wFast else null end MovingAverageFast,
case when count(*) over wSlow > 13 then avg(ClosingPrice) over wSlow else null end as MovingAverageSlow
from StockPrice
window wFast as (order by StockSymbol, TradeDate rows between 6 preceding and current row),
wSlow as (order by StockSymbol, TradeDate rows between 13 preceding and current row)
If you wish, you can compare the fast and slow moving averages to indicate a trend. When the fast-moving average is greater than the slow moving average, the price is trending up.
I added this simple comparison as a case statement. You see that I add the two together to determine whether both average as present, as if one or both are null, the result is null.
select *,
case when MovingAverageFast + MovingAverageSlow is null then 'Unknown'
when MovingAverageFast > MovingAverageSlow then 'Up' else 'Down' end Trend
from (
select TradeDate, ClosingPrice,
case when count(*) over wFast > 6 then avg(ClosingPrice) over wFast
else null end MovingAverageFast,
case when count(*) over wSlow > 13 then avg(ClosingPrice) over wSlow
else null end MovingAverageSlow
from StockPrice
window wFast as (order by StockSymbol, TradeDate rows between 6 preceding and current row),
wSlow as (order by StockSymbol, TradeDate rows between 13 preceding and current row)
) d
Moving Averages in SQL Conclusion
Comparing moving averages is a useful tool for identifying trends and potential buy/sell signals in financial analysis. In this example, we demonstrated how to calculate the 7-day moving average and 14-day moving average for Tesla stock prices using SQL window functions.
The window function method is more efficient and easier to read and understand than using subqueries or self-joins. However, it is important to note that there may be situations where alternative methods are necessary depending on the specific requirements of the analysis.
Overall, understanding how to calculate moving averages in SQL is an important skill for anyone involved in financial analysis or data analysis in general.
Leave a Reply