Introduction
Basic CTEs
9. Multiple CTEs
Summary

Instruction

Excellent. You can actually 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 SomeName1 AS ( YourCTE1 ),
SomeName2 AS ( YourCTE2 ),
...
SELECT ...

Do 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.

Introducing multiple CTEs usually makes sense when they refer to each other. We'll get to know such CTEs in the next part. For now, consider other usages: for instance, you can use set operations like UNION to show results from both CTEs.

Suppose we want to show the top projects from two separate categories and some specific parameters. We could provide a query like this:

WITH SuccTravelling AS (
  SELECT
    ProjectId,
    Category,
    SUM(Amount) AS SumAmount
  FROM Project
  INNER JOIN Donation
  ON Donation.ProjectId = Project.Id
  WHERE Category = N'Travelling'
  GROUP BY ProjectId, Category, MinimalAmount
  HAVING SUM(Amount) >= 1.25 * MinimalAmount
),

SuccGames AS (
  SELECT
    ProjectId,
    Category,
    SUM(Amount) AS SumAmount
  FROM Project
  INNER JOIN Donation
    ON Donation.ProjectId = Project.Id
  WHERE Category = N'Games'
  GROUP BY ProjectId, Category, MinimalAmount
  HAVING SUM(Amount) >= 2 * MinimalAmount
)

SELECT
  ProjectId,
  Category,
  SumAmount
FROM SuccTravelling
UNION
SELECT
  ProjectId,
  Category,
  SumAmount
FROM SuccGames
ORDER BY SumAmount DESC;

In this example, we want to show projects within the travelling category, that collected at least 25% more money than the MinimalAmount: we're also showing projects from the games category that collected at least twice as much money as they initially needed. Once we've defined both CTEs, we can show all results together by using UNION in the outer query.

Exercise

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

Stuck? Here's a hint!

Create two CTEs: one for January, and one for February. Join the Donation and Project tables to get the desired result.

In order to check if a donation occurred in January 2016, use: WHERE DonateDate BETWEEN '2016-01-01' AND '2016-01-31'.

Remember that February 2016 had 29 days.