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!
Basic CTE
7. Syntax with columns


Great. Now, there is an alternative CTE syntax where we define the columns explicitly:

WITH some_name (cte_columns) AS ( your_cte )
  SELECT ... 
  FROM some_name

In other words, we now have two pairs of parentheses. First, we provide the names of columns that our CTE will have. Second, we define the actual CTE based on the columns we provided.

WITH project_revenue (id, sum_amount) AS (
  FROM project
  JOIN donation 
  ON donation.project_id =

FROM project_revenue;

The columns definition is not required. Why would we use it, then? For one thing, it increases the readability of your query. For another, while simple columns inside CTEs don't require aliases, aggregates and other function results do. Columns like SUM(amount) or COUNT(project_id) must be given names so that you can refer to them outside CTEs. One way is to use the keyword AS, just as we did previously. Another way is to provide the list of columns. Either method is fine, but if you use none - an error will occur.


Check it for yourself: try to run the template query without columns definition or aliases and see what happens. Then, correct it and run the code again.