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 product1WITH sales_summary AS (2 SELECT3 product_id, -- Add up the quantities of all orders of the product4 SUM(quantity) AS total_qty5 FROM orders -- One row per product_id6 GROUP BY product_id-- A comma separates the CTEs; WITH is written only once7),-- Second CTE: the product details needed for the report8product_info AS (9 SELECT10 id,11 name,12 category13 FROM products14)-- Main query: name, category and quantity sold15SELECT16 p.name,17 p.category,18 s.total_qty-- Use the first CTE like a table, with alias s19FROM20 sales_summary s-- JOIN (= INNER JOIN) links each sales total to its product-- Products that were never ordered do not appear21JOIN22 product_info p ON s.product_id = p.id-- Best-selling products first23ORDER BY24 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. 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