In this puzzle, we're going to learn how to calculate moving averages by working through a hypothetical stock market example.
Solving puzzles is a great way to learn SQL. Nothing beats practicing what you've learned. Once you have figured out the puzzle, post your answer in the comments so we can all learn from one another. We also discuss puzzles and more in Essential SQL Learning Group on FaceBook. Be sure to find us there!
SQL Puzzle Question
It's your first day on the job and an analyst has given you a set of closing stock quotes for Microsoft. She would like you to give her a report showing the closing data, closing price, 10-day moving average, 30-day moving average, and signal.
The signal is either "
Over" or "
Below" depending on whether the 10-day moving average is greater than or less than the 30-day moving average respectively.
You output should look like this:
Use the following table variable to work the problem:
DECLARE @DailyQuote TABLE
Download this script to create the table and populate it with sample data.
What query would you write to calculate moving averages?
Answer to Calculate Moving Averages
Before we get into the SQL, let's make sure we know what we're calculating! To calculate the 10-day moving average of the closing price, we need to calculate the prices of current and past 9 days closing prices. We do the same for the 30-day moving average, but in that case, we'll include more days.
An easy way to calculate the moving average is to set up a window. We can do this with the
Below is the statement to calculate the 10-day moving average
AVG(ClosingPrice) OVER (ORDER BY MarketDate ASC ROWS 9 PRECEDING) AS MA10
OVER clause, we order the rows by
MarketDate, then use the
PRECEDING clause to define the window as starting with the current row and then going nine rows up. This makes the window 10 rows in total.
You can see how this works in the diagram below:
One thing that bugs me with this method is that the 10-day moving average is calculated for the first several rows in the result; the average is a one day, two day, three day moving average, and so on until we actually get to the tenth day.
Technically, it should start to calculate until we are on the tenth row. To account for this, I also compute the
ROW_NUMBER, and if the
ROW_NUMBER is less than
To do this, I compute the
ROW_NUMBER, 10-day, 30-day within a common table expression. You'll see this in the green portion below.
WITH CTE_DailyQuote (MarketDate, ClosingPrice, RowNumber, MA10, MA30)
ROW_NUMBER() OVER (ORDER BY MarketDate ASC) RowNumber,
AVG(ClosingPrice) OVER (ORDER BY MarketDate ASC ROWS 9 PRECEDING) AS MA10,
AVG(ClosingPrice) OVER (ORDER BY MarketDate ASC ROWS 29 PRECEDING) AS MA30
IIF(RowNumber > 9, MA10, NULL) MA10,
IIF(RowNumber > 29, MA30, NULL) MA30,
WHEN RowNumber > 29 AND MA10 > MA30 THEN 'Over'
WHEN RowNumber > 29 AND MA10 < MA30 THEN 'Below'
END as Signal
ORDER BY MarketDate
With the results from the CTE (Common Table Expression), I'm able to compare the
ROW_NUMBER and return a
The last item to do is to generate the
Signal. To do this, I used a
CASE statement. It's a matter of simply comparing the 10-day moving average to the 30-day with the added twist of ensuring we are already at or beyond the 30th result row.
Here is a sampling of results:
There are many other ways to calculate a Moving Average. What ways have you done? Please let me know in the comments! We also discuss puzzles and more in Essential SQL Learning Group on FaceBook. Be sure to find us there!
The post SQL Puzzle: How to Calculate Moving Averages appeared first on Essential SQL.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/