Window Functions
Window Functions — Master SQL window functions — ROW_NUMBER, RANK, LEAD/LAG, NTILE, and frame specifications. The guide walks through ROW_NUMBER(), RANK() & DENSE_RANK(), LEAD() & LAG(), NTILE() & Bucketing, FIRST_VALUE() & LAST_VALUE(), Frame Specifications. ROW_NUMBER() assigns a unique sequential integer to each row within a partition. The numbering starts at 1 for the first row in each partition. Syntax: ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC). Unlike RANK, ROW_NUMBER() never produces ties — every row gets a unique number. Use it for pagination, deduplication, and top-N-per-group queries. RANK() assigns the same number to tied rows but skips numbers after ties. If two rows tie for rank 1, the next rank is 3. DENSE_RANK() assigns the same number to ties but never skips — the next rank after a tie is 2. Use RANK when total position matters and DENSE_RANK when you want consecutive rankings. The guide is organized into 6 sections that build on each other, each pairing a prose explanation with real SQL you can run as-is.