Advanced SQL
CTEs, window functions, and the patterns that separate junior analysts from senior data engineers.
0/13 done
Common Table Expressions (CTE)
WITH ... ASName your intermediate results and build complex queries in readable steps.
Complete EXISTS to unlock
Recursive CTEs
WITH RECURSIVEQuery hierarchical data and generate sequences using a CTE that references itself.
Complete Common Table Expressions (CTE) to unlock
Window Functions I — Ranking
ROW_NUMBER / RANK / DENSE_RANKRank rows within groups using ROW_NUMBER, RANK, and DENSE_RANK.
Complete Recursive CTEs to unlock
Window Functions II — LAG, LEAD & Running Totals
LAG / LEAD / SUM OVERCompare rows to their neighbors and compute cumulative values without subqueries.
Complete Window Functions I — Ranking to unlock
UNION & UNION ALL
UNION / UNION ALLStack result sets from multiple queries vertically into one table.
Complete Window Functions II — LAG, LEAD & Running Totals to unlock
Views
CREATE VIEWSave a query as a named virtual table that anyone can query like a real table.
Complete UNION & UNION ALL to unlock
Transactions
BEGIN / COMMIT / ROLLBACKGroup statements into one atomic unit — all succeed or all roll back.
Complete Views to unlock
Constraints: CHECK & Foreign Keys
CHECK / FOREIGN KEY / ON DELETELet the database itself enforce your business rules, so bad data can never get in.
Complete Transactions to unlock
Stored Procedures
CREATE PROCEDURE / CALLPackage multi-step logic — including transactions — as a single callable unit.
Complete Constraints: CHECK & Foreign Keys to unlock
Triggers
CREATE TRIGGERRun logic automatically whenever a row is inserted, updated, or deleted.
Complete Stored Procedures to unlock
Materialized Views
MATERIALIZED VIEW / REFRESHCache an expensive query's result physically on disk, and refresh it on your own schedule.
Complete Triggers to unlock
Pivoting Data with CASE
Conditional aggregation pivotTurn row values into columns — the classic spreadsheet pivot table, written in SQL.
Complete Materialized Views to unlock