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 in PostgreSQL

Introduction
Basic CTE
6. Basic syntax – exercise
Summary

Instruction

Very good. Let's do one more exercise.

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 names of authors along with the number of not-yet-founded projects they've created. Name this column projects_count and show it as the third column. Show the authors in descending order based on projects_count.

Stuck? Here's a hint!

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

Recall that "not-yet-funded projects" are projects with a current total sum of donations that's smaller than the stated minimal_amount.