Cheat Sheet logoCheat Sheet Today
MySQL Joins, Subqueries, and CTEs Cheatsheet/Use a CTE to isolate recent orders

Create a readable query pipeline with `WITH`.

Back to sheetBrowse commands
Sign in to save
Section: subqueries and ctes

Use a CTE to isolate recent orders

sql
sql
WITH recent_orders AS (
  SELECT id, user_id, total_amount, created_at
  FROM orders
  WHERE created_at >= NOW() - INTERVAL 30 DAY
)
SELECT user_id, COUNT(*) AS recent_order_count, SUM(total_amount) AS revenue
FROM recent_orders
GROUP BY user_id;
Explanation

CTEs help break complex logic into named steps.

View in sheet →

Learn the surrounding workflow

Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.

Compare commandsView fixes

Related commands

Same sheet · prioritizing subqueries and ctes
Filter with a subquery
Return users who have at least one paid order.
OpenIn sheetsqlsame section
Use EXISTS for correlated filtering
Return users who have orders.
OpenIn sheetsqlsame section
Join to a derived table
Compute an aggregate once and join it back.
OpenIn sheetsqlsame section
Inner join two tables
Return rows where both tables match.
OpenIn sheetsql
Left join to keep unmatched parent rows
Return all rows from the left table and matching rows from the right table.
OpenIn sheetsql
Join and aggregate child rows
Count child rows per parent.
OpenIn sheetsql