SQLite Joins, CTEs, and Window Functions Cheatsheet

SELECT p.id, p.title, u.email
FROM posts p
JOIN users u ON u.id = p.user_id;

# Return rows that match in both tables.

SELECT u.id, u.email, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id;

# Keep parent rows even if the child is missing.

SELECT u.id, u.email, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.email;

# Count child rows per parent.

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

# Name an intermediate result set.

WITH RECURSIVE category_tree(id, name, parent_id, depth) AS (
  SELECT id, name, parent_id, 0 FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

# Walk a hierarchy such as categories or folders.

SELECT id, email, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num
FROM users;

# Rank rows within an ordered result.

SELECT created_at,
       amount,
       SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM invoices;

# Use SUM() OVER for cumulative metrics.

SELECT player_id, score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS leaderboard_rank
FROM scores;

# Assign rank values with ties.