Basic CTE
Instruction
Very good. Let's do one more exercise.
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 names of authors along with the number of not-yet-founded projects they've created. Name this column projects_count and show it as the third column. Show the authors in descending order based on projects_count.
Stuck? Here's a hint!
In the CTE, join the project and donation tables. To calculate the sum of donations, be sure to group by the author's ID, the project's ID and the minimal_amount.
Recall that "not-yet-funded projects" are projects with a current total sum of donations that's smaller than the stated minimal_amount.
