{"id":3969,"date":"2025-06-07T08:30:48","date_gmt":"2025-06-07T08:30:48","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=3969"},"modified":"2025-06-07T10:00:39","modified_gmt":"2025-06-07T10:00:39","slug":"sql-window-functions","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/sql\/sql-window-functions\/","title":{"rendered":"SQL Window Functions"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Window functions let you perform calculations <strong>across rows related to the current row<\/strong>, without collapsing the result like GROUP BY does. They\u2019re great for ranking, running totals, and more.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd39 Key Window Functions<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>ROW_NUMBER()<\/code> \u2014 assigns a unique sequential number to rows in a partition<\/li>\n\n\n\n<li><code>RANK()<\/code> \u2014 ranks rows, with gaps for ties<\/li>\n\n\n\n<li><code>DENSE_RANK()<\/code> \u2014 ranks rows without gaps<\/li>\n\n\n\n<li><code>NTILE(n)<\/code> \u2014 divides rows into <em>n<\/em> groups<\/li>\n\n\n\n<li><code>LAG()<\/code> \/ <code>LEAD()<\/code> \u2014 access previous\/next row\u2019s data<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd39 Basic Syntax<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SELECT\n  column1,\n  ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column) AS row_num,\n  RANK() OVER (ORDER BY order_column DESC) AS rank\nFROM table_name;<\/code><\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>PARTITION BY<\/code> divides data into groups (like GROUP BY, but rows remain visible)<\/li>\n\n\n\n<li><code>ORDER BY<\/code> defines row order within each partition<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd39 Example: Ranking Employees by Salary in Each Department<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SELECT\n  employee_name,\n  department_id,\n  salary,\n  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,\n  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank\nFROM employees;<\/code><\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>ROW_NUMBER<\/code> assigns unique rank<\/li>\n\n\n\n<li><code>RANK<\/code> assigns same rank to ties, skips numbers after ties<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83e\udde0 Quick Recap<\/h2>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Function<\/th><th>Description<\/th><\/tr><\/thead><tbody><tr><td><code>ROW_NUMBER()<\/code><\/td><td>Unique row number per partition<\/td><\/tr><tr><td><code>RANK()<\/code><\/td><td>Ranking with gaps for ties<\/td><\/tr><tr><td><code>DENSE_RANK()<\/code><\/td><td>Ranking without gaps<\/td><\/tr><tr><td><code>NTILE(n)<\/code><\/td><td>Divides rows into <em>n<\/em> buckets<\/td><\/tr><tr><td><code>LAG()\/LEAD()<\/code><\/td><td>Access previous\/next row\u2019s value<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udca1 Window functions power up your queries with advanced analytics \u2014 all while keeping rows intact!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Window functions let you perform calculations across rows related to the current row, without collapsing the result like GROUP BY does. They\u2019re great for ranking, running totals, and more. \ud83d\udd39 Key Window Functions \ud83d\udd39 Basic Syntax \ud83d\udd39 Example: Ranking Employees by Salary in Each Department \ud83e\udde0 Quick Recap Function Description ROW_NUMBER() Unique row number per [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,979],"class_list":["post-3969","cposts","type-cposts","status-publish","hentry","category-notes","category-sql"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3969","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}