Save up to $499! Grab all Python courses for $49 or all online courses we’ve ever launched for only $169. Only Feb 11-16. Happy Valentine's!
Introduction
Basic CTE
5. Basic syntax
Summary

Instruction

Perfect. 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 some_name AS (your_cte)
  SELECT ... 
  FROM some_name

In the most basic version, you need to give your CTE some_name and define it within the parentheses. Then, once you close the bracket, you can select columns from this CTE as if it were a table. 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 project_revenue AS (
  SELECT
    project.id,
    SUM(amount) AS sum_amount
  FROM project 
  JOIN donation 
  ON donation.project_id = project.id
  GROUP BY project.id
)

SELECT
  id,
  sum_amount
FROM project_revenue;

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 project_id and the sum of donations. Once we had our CTE defined this way, we retrieve its columns in the outer query. Note that we gave an alias (sum_amount) to the computed column. This way we can refer to this column in the outer query.

Exercise

Your turn now! Show the number of projects which reached the minimal_amount.

Stuck? Here's a hint!

In the inner query, group and sum donations by the project_id and use a HAVING clause to find the right ones. In the outer query, count the rows.