Common Table Expression in SQL

Level 5203 keystrokes

WITH clause creates a CTE (Common Table Expression) that can be referenced like a temporary table.

SQL code

-- WITH defines a named query, monthly_sales, used below like a table
1WITH monthly_sales AS (
2 SELECT
-- Turn each order date into a 'YYYY-MM' month label
3 DATE_FORMAT(order_date, '%Y-%m') AS month,
-- Total revenue of the month
4 SUM(total) AS revenue
5 FROM
6 orders
-- One group per month (MySQL allows the SELECT alias here)
7 GROUP BY
8 month
9)
-- The main query reads the CTE like a normal table
10SELECT
11 month,
12 revenue
13FROM
14 monthly_sales
-- Keep only months with revenue over 10000
15WHERE
16 revenue > 10000
-- 'YYYY-MM' text sorts in calendar order
17ORDER BY
18 month;

Output

+---------+----------+
| month   | revenue  |
+---------+----------+
| 2024-01 | 15000.00 |
| 2024-02 | 12500.00 |
| 2024-03 | 18000.00 |
+---------+----------+
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.

  • _× 4
  • ,× 3
  • (× 3
  • )× 3
  • '× 2
  • %× 2
  • -× 1
  • ;× 1
  • >× 1

More SQL examples at level 5

← SQL typing practice