Imagine you are working as an analyst at a large bank (if you aren’t already, that is). Let’s say you are required to find out your top 10 clients out of thousands. Of these clients, your company wants to give some cash handouts to the one that has the least turnover in the current year. You can definitely use a simple query to achieve this [by using the min() function and the ‘where’ clause], but what if you want to derive multiple insights from the data of your top 10 clients? In this case, it is better to use a Common Table Expression (CTE), so that you can get the data of only those clients and perform all of your analysis on a subset of the entire data. In the upcoming video, you will learn more about CTEs from our expert.
A CTE is used to create a temporary table, which is smaller than the existing table. This smaller table cannot be individually queried, that returns an error. The CTE has to be used as part of the main query.
In this way, you have learnt about the CTEs, let’s try to understand views in the next segment.