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 table1WITH monthly_sales AS (2 SELECT -- Turn each order date into a 'YYYY-MM' month label3 DATE_FORMAT(order_date, '%Y-%m') AS month, -- Total revenue of the month4 SUM(total) AS revenue5 FROM6 orders -- One group per month (MySQL allows the SELECT alias here)7 GROUP BY8 month9)-- The main query reads the CTE like a normal table10SELECT11 month,12 revenue13FROM14 monthly_sales-- Keep only months with revenue over 1000015WHERE16 revenue > 10000-- 'YYYY-MM' text sorts in calendar order17ORDER BY18 month;Output
+---------+----------+ | month | revenue | +---------+----------+ | 2024-01 | 15000.00 | | 2024-02 | 12500.00 | | 2024-03 | 18000.00 | +---------+----------+ 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.
_× 4,× 3(× 3)× 3'× 2%× 2-× 1;× 1>× 1