You must have noticed that there was a ‘partition by’ clause in the syntax for the rank functions. But what exactly is this clause used for? You will learn more about this in the upcoming video.
As you learnt in this video, the ‘rank’ and ‘dense rank’ functions are used with the ‘over’ clause. However, this may not be enough if you want to rank groups of rows based on certain criteria. For example, you can rank the top 10 batsmen in the world using the ‘rank’ function. But what if you want to find out the top three batsmen from each team? This is where you would want to use the ‘partition’ and ‘over’ clauses together. So, let’s watch the next video and learn more about these clauses and their uses.
So, now that you have learnt about window functions and partitioning, try to attempt the following question.
Try to answer the question given below to get more comfortable with the syntax. You may find it a bit difficult initially, but partitions and windows are a useful tool to solve questions that require you to rank values, especially when you need to rank values separately based on certain criteria (for example, state or gender).
Individual and Total Order Amounts
In the next segment, you will learn about named windows and how they shorten long queries, making them more readable.