Multiple CTEs in SQL

Level 5294 keystrokes

Multiple CTEs can be defined and then used together in the main query.

SQL code

-- First CTE: total quantity sold per product
1WITH sales_summary AS (
2 SELECT
3 product_id,
-- Add up the quantities of all orders of the product
4 SUM(quantity) AS total_qty
5 FROM orders
-- One row per product_id
6 GROUP BY product_id
-- A comma separates the CTEs; WITH is written only once
7),
-- Second CTE: the product details needed for the report
8product_info AS (
9 SELECT
10 id,
11 name,
12 category
13 FROM products
14)
-- Main query: name, category and quantity sold
15SELECT
16 p.name,
17 p.category,
18 s.total_qty
-- Use the first CTE like a table, with alias s
19FROM
20 sales_summary s
-- JOIN (= INNER JOIN) links each sales total to its product
-- Products that were never ordered do not appear
21JOIN
22 product_info p ON s.product_id = p.id
-- Best-selling products first
23ORDER BY
24 s.total_qty DESC;

Output

+--------+-------------+-----------+
| name   | category    | total_qty |
+--------+-------------+-----------+
| Laptop | Electronics |       150 |
| Phone  | Electronics |       120 |
| Shirt  | Clothing    |        80 |
+--------+-------------+-----------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • _× 10
  • ,× 6
  • .× 6
  • (× 3
  • )× 3
  • ;× 1
  • =× 1

More SQL examples at level 5

← SQL typing practice