Introduction
Basic CTE
6. Basic syntax - exercise
Summary

Instruction

Very good. Let's do one more exercise to warm you up.

Remember, a CTE query starts with WITH, as in the 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;

Exercise

Show the first and last name of authors with the number of not yet founded projects they created. Show projects_count as the third column. Show the authors in descending order of projects_count

Stuck? Here's a hint!

In the CTE, join project with donation tables and group by the author's ID, the project's ID and the minimal_amount to be able to calculate the sum of donations.

Recall that "not yet founded projects" are projects with the sum of donations smaller than minimal_amount.