It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic CTE
6. Basic syntax – exercise
Summary

## 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.