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

Instruction

Excellent. Now, let's do another kind of exercise with multiple CTEs. This time, you will have to use a JOIN in the outer query.

Exercise

For each person who made donations in the 'music' or 'traveling' categories, show three columns:

  • supporter_id
  • min_music – That person's minimum donation amount in the music category.
  • max_traveling – That person's maximum donation amount in the traveling category.

Stuck? Here's a hint!

First, use a CTE to find all users that donated to either music or traveling projects. Then, use two CTEs – one for finding the minimum for music category, and one for finding the maximum in traveling category. In each CTE, select the supporter_id. In the outer query, join all your CTEs' results on supporter_id.

Use LEFT JOIN to show all the results.