Basic CTEs
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.
