On January 21th at 14:15 UTC , progression through exercises will be unavailable for 10 minutes due to a planned maintenance break.
Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Basic CTEs
6. Basic syntax – exercise


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

FROM ProjectRevenue;


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 Donation and Project tables, and group by the AuthorID, ProjectId, and MinimalAmount to be able to calculate the sum of all donations by the author in a given project.

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