Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries

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. Remember, the author must be a supporter.

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.

Recall that "not yet founded projects" are projects with the sum of donations smaller than minimal_amount.