Introduction
Basic CTE
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. Remember, the author must be a supporter.
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.
