Introduction
Basic CTEs
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 ProjectRevenue AS (
SELECT
Project.Id,
SUM(Amount) AS SumAmount
FROM Project
INNER JOIN Donation
ON Donation.ProjectId = Project.Id
GROUP BY Project.Id
)

SELECT
Id,
SumAmount
FROM ProjectRevenue;


## Exercise

For all authors, show their first and last names and the number of not-yet-funded projects they've created. Call this column ProjectsCount. Sort the authors in descending order, based on ProjectsCount.

### Stuck? Here's a hint!

In the inner query, join the Project and Supporter tables, and group by the Author and Category to be able to calculate the count.

Remember: every author had to be a supporter at least once, so you'll find their names in the Supporter table.