Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
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 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.