In the previous segment, you were introduced to the concept of windowing functions and windows. You also learnt how to use basic aggregate functions, including the ‘count()’ function, in windowing.
In this segment, you will learn about the concept of frames and understand how frames move while a query is being executed. You will also learn how to implement moving averages in SQL. You can get a rough idea of the importance of calculating moving averages, especially in the stock market sector, by clicking on the link provided below and quickly going through the article on Investopedia.
Note that you will only be calculating simple moving averages in this course. In the upcoming video, Shreyas will explain the concept of frames with the help of an example.
In this video, you learnt how frames are useful in SQL and when they can be applied to answer relevant questions asked by stakeholders. In the next video, you will see a demonstration of how to use frames in SQL.
So, in this video, you learnt how to use frames in SQL. Now, attempt the following question to test your understanding of moving averages.
Try to solve the question given below; it will help you analyse Kohli’s batting in ODIs over a period of five years. You will be required to display 3 columns: ‘Year‘, ‘Runs‘ and ‘5 Year Moving Average‘ for the given data.
If you look at the output obtained after writing the correct query, you will notice that Kohli’s five-year average has been about 1,000 since 2013. This indicates that he has been a consistent player. Similarly, you can write queries to obtain one-year moving averages to extract and understand any trend in the data in the question given above.
In the next segment, Shreyas will break down the frame clause and analyse its components. This will help you to understand frames better and use the appropriate keywords and solve problems.