Save up to $499! Grab all Python courses for $49 or all online courses we’ve ever launched for only $169. Only Feb 11-16. Happy Valentine's!
Introduction
Basic CTE
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 project_revenue AS (
  SELECT
    project.id, 
    SUM(amount) AS sum_amount
  FROM project
  JOIN donation
  ON donation.project_id = project.id
  GROUP BY project.id
)

SELECT
  id,
  sum_amount
FROM project_revenue;

Exercise

Show the first and last name of authors with the number of not yet founded projects they created. Show projects_count as the third column. Show the authors in descending order of projects_count

Stuck? Here's a hint!

In the CTE, join project with donation tables and group by the author's ID, the project's ID and the minimal_amount to be able to calculate the sum of donations.