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.