Databases
sql
Beginner
Common Table Expression Patterns
Practical CTE examples: recursive hierarchy, deduplication, and windowed aggregates.
sql
-- Deduplication
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM users
)
SELECT * FROM ranked WHERE rn = 1;
-- Recursive tree
WITH RECURSIVE tree AS (
SELECT id, name, 0 AS depth FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, t.depth+1 FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth;Prerequisites
- PostgreSQL >= 12 or MySQL >= 8
Pro Tips
- → Add MATERIALIZED hint in Postgres for large CTEs.
Commercial Licensing
Acquire a production access token or template license for enterprise deployment.
Module Info
- Language
- sql
- Level
- Beginner
- Read time
- 4 min
- Published
- Mar 2025
- License
- Commercial License
Newsletter
New modules every week, directly to your inbox.
Similar Modules
MySQL Replication Status Check
Script to check MySQL/MariaDB replication lag and alert if it exceeds threshold.
sql
·
Intermediate
DB Connection Pool Monitor
Query to check active connections, idle connections and pool usage in PostgreSQL.
sql
·
Intermediate
Cursor-based Pagination Query
Implement cursor-based (keyset) pagination in SQL — faster than OFFSET for large tables.
sql
·
Intermediate