Perfect! It's time to get down to work.

What is a Common Table Expression? You can think of it as a temporary set of rows that you define on your own and then use in the same query. In principle, CTEs are similar to subqueries.

Let's take a look at the most basic syntax of any Common Table Expression:

WITH SomeName AS (
  YourCte
)

SELECT
  ...
FROM SomeName

In the most basic version, you need to give your CTE a name and define the CTE within the parentheses. Then, once you close the bracket, you can select the columns from within this CTE as if it were a table.

In this course, we will refer to the CTE part as the inner query and the part after it as the outer query. Note that you need to define your CTE first, i.e. before the SELECT clause of the outer query.

Enough of theory. Take a look at this example:

WITH ProjectRevenue AS (
  SELECT
    Project.Id,
    SUM(Amount) AS SumAmount
  FROM Project
  INNER JOIN Donation
    ON Donation.ProjectId = Project.Id
  GROUP BY Project.Id
)

SELECT
  Id,
  SumAmount
FROM ProjectRevenue;

In the query above, we want to show each project with the amount it collected. For this reason, we created a CTE where we selected the ProjectId and the sum of donations. Once we had our CTE defined this way, we retrieved its columns in the outer query. Note that we gave an alias (SumAmount) to the computed column. This way, we can refer to this column in the outer query.