It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic CTE
9. Multiple CTEs
Summary

Instruction

Excellent. You can have as many CTEs in a single query as you need. Each of them should be separated with a comma, and the WITH keyword should only appear once, at the beginning.

WITH some_name1 AS ( your_CTE1 ),
some_name2 AS ( you_CTE2 ),
...
SELECT ...

Remember that WITH appears only once, at the beginning. The other CTEs are separated with commas. Do not put a comma after the last CTE.

Using multiple CTEs usually makes sense when they refer to each other. We'll get to know such CTEs in the next part. For now, we may think of other usages; for instance, you can use set operations like UNION to show results from two CTEs. Suppose we want to show the top projects from two separate categories and some specific parameters. To do so, we could use a query like this:

WITH succ_traveling AS (
  SELECT
    project_id,
    category,
    SUM(amount) AS sum_amount
  FROM project
  JOIN donation
    ON donation.project_id = project.id
  WHERE category = 'traveling'
  GROUP BY project_id, category, minimal_amount
  HAVING SUM(amount) >= 1.25 * minimal_amount),

succ_games AS (
  SELECT
    project_id,
    category,
    SUM(amount) AS sum_amount
  FROM project 
  JOIN donation 
    ON donation.project_id = project.id
  WHERE category = 'games'
  GROUP BY project_id, category, minimal_amount
  HAVING SUM(amount) >= 2 * minimal_amount)

SELECT 
  project_id, 
  category, 
  sum_amount
FROM succ_traveling
UNION
SELECT 
  project_id, 
  category, 
  sum_amount
FROM succ_games
ORDER BY sum_amount DESC;

In this example, we want to show projects from the traveling category that collected at least 25% more than their minimal_amount. We also want to show projects from the games category that collected at least twice as much as their minimal_amount. Once we defined both CTEs, we could show all results by using UNION in the outer query.

Exercise

In January 2016, supporters who donated at least 10% of a project's minimal_amount in one donation received a gift. In February 2016, they had to donate 20% of the minimal_amount to get the same gift. Show the columns amount and donated for all donations occurring in January and February for which a gift was awarded.

Stuck? Here's a hint!

Create two CTEs: one for January and one for February. Join donation with project to get the desired result.

To check if a donation occurred in January 2016, use:

WHERE donated BETWEEN '2016-01-01' AND '2016-01-31'

Remember that February 2016 had 29 days.