Advanced T-SQL: Window Functions for Advanced Querying

Learn how to apply the row_number window function to rank tracks within ordered windows, using the Chinook track table to demonstrate pagination, partitioning, and order by.

explain how rank and dense_rank window functions assign ranks within partitions, compare them to row_number, and highlight the effects of ties, skipped ranks, and filtering challenges.

Explore the ntile window function, which partitions rows into roughly equal buckets based on a parameter, assigns ranks, and supports practical use like price-based discounts.

Explore window aggregate functions in SQL Server, including average, count, max, min, and sum. Compare windowing with traditional group by and see how window functions preserve detail without losing rows.

Explore how window aggregate functions operate with over clauses to partition data, compare aggregates within categories, and retrieve related columns via joins and CTEs in the bike stores database.

Explore window frames in t-sql window functions, defining frames with rows and range operators, unbounded preceding and following, and current row to compute running totals and time-period comparisons.

Explore window aggregate functions with frames to build running totals and year-over-year analyses, using range and rows frames, order by year, and CTEs for practical reports.

Explore lag and lead window functions to fetch prior and following rows with offset and default options. Apply these to analyze revenue growth across years and quarters.

Explore first_value and last_value window functions in T-SQL. Learn how frame specifications and partitioning by customer and year reveal the first and last orders.

Explore percent_rank and cume_dist window functions in SQL Server, computing relative standings within partitions using order by, with examples on product prices and city revenues.

Explore inverse distribution window functions, percentile discrete function and percentile continuous function, learn their discrete and continuous models, and apply them to compute medians and percentiles within partitions.

Review window concepts and functions, including ranking (row_number, rank, dense_rank, end tile ranking function), aggregates, frames, offsets (lag, lead, first_value, last_value), and statistical functions, ending with assessments and certificate.