Cheat Sheet logoCheat Sheet Today
SQLite Joins, CTEs, and Window Functions Cheatsheet/Use a CTE to stage a subquery

Name an intermediate result set.

Back to sheetBrowse commands
Sign in to save
Section: CTEs and window functions

Use a CTE to stage a subquery

sql
sql
WITH recent_posts AS (
  SELECT * FROM posts WHERE created_at >= date('now', '-7 day')
)
SELECT COUNT(*) FROM recent_posts;
Explanation

CTEs improve readability for multi-step transformations.

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 CTEs and window functions
Use a recursive CTE
Walk a hierarchy such as categories or folders.
OpenIn sheetsqlsame section
Assign row numbers
Rank rows within an ordered result.
OpenIn sheetsqlsame section
Compute a running total
Use SUM() OVER for cumulative metrics.
OpenIn sheetsqlsame section
Rank rows by score
Assign rank values with ties.
OpenIn sheetsqlsame section
Use an INNER JOIN
Return rows that match in both tables.
OpenIn sheetsql1 tag match
Use a LEFT JOIN
Keep parent rows even if the child is missing.
OpenIn sheetsql1 tag match