Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
Window Functions 1: OVER, PARTITION BY, ROW_NUMBER, RANK and Top-N per Group
A window function computes like GROUP BY but does not collapse rows; it writes the result next to each row. We show OVER and PARTITION BY, how ROW_NUMBER, RANK, DENSE_RANK and NTILE differ on ties, why they cannot appear in WHERE, top-N per group with a CTE, the tie decision, and safe duplicate removal with ROW_NUMBER on Bookstore data with real output.
Window Functions 2: Running Totals, ROWS vs RANGE Frames, LAG/LEAD and Gaps and Islands
The default frame of a window aggregate with ORDER BY is RANGE UNBOUNDED PRECEDING; rows with equal values get the same total and SQL Server uses a worktable. We show running totals, the logical-read difference between ROWS and RANGE, moving averages with missing days, LAG/LEAD, the LAST_VALUE trap, medians with PERCENTILE_CONT, and gaps and islands on Bookstore data with real output.