Advanced SQL

Common Table Expressions (CTEs)

Name your query steps with WITH — no more nested subquery spaghetti

Introduction to Window Functions

Add aggregated or ranked values to every row — without collapsing anything

ROW_NUMBER()

Assign unique sequential numbers — and solve top-N-per-group

RANK()

Rank rows with tied values getting the same position — gaps after ties

DENSE_RANK()

Rank without gaps — consecutive numbers even when values tie

LAG()

Access the previous row's value — essential for trend analysis

LEAD()

Look ahead to the next row's value — the mirror of LAG