Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Use Window Functions for Advanced Analytical Queries

- 30.03.26 - ErcanOPAK

📊 SQL Superpowers

Need row numbers? Running totals? Rankings? Window functions perform calculations across table rows without GROUP BY. Game-changing for analytics.

ROW_NUMBER() – Assign Row Numbers

-- ❌ Old way: Can't number rows easily
SELECT name, salary, department
FROM employees
ORDER BY salary DESC;

-- ✅ Window function: Add row numbers
SELECT 
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  name,
  salary,
  department
FROM employees;

-- Result:
-- row_num | name    | salary | department
-- 1       | Alice   | 120000 | Engineering
-- 2       | Bob     | 110000 | Engineering
-- 3       | Charlie | 100000 | Sales
-- 4       | David   | 95000  | Marketing

-- Row number per department
SELECT 
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  name,
  salary,
  department
FROM employees;

-- Result: Separate numbering per department
-- dept_rank | name    | salary | department
-- 1         | Alice   | 120000 | Engineering
-- 2         | Bob     | 110000 | Engineering
-- 1         | Charlie | 100000 | Sales
-- 1         | David   | 95000  | Marketing

📈 RANK() and DENSE_RANK()

-- RANK() - Gaps in ranking for ties
SELECT 
  RANK() OVER (ORDER BY score DESC) AS rank,
  name,
  score
FROM students;

-- score: 100, 100, 95, 90
-- rank:  1,   1,   3,  4  (gap at 2!)

-- DENSE_RANK() - No gaps
SELECT 
  DENSE_RANK() OVER (ORDER BY score DESC) AS rank,
  name,
  score
FROM students;

-- score: 100, 100, 95, 90
-- rank:  1,   1,   2,  3  (no gap)

-- Top 3 salaries per department
SELECT *
FROM (
  SELECT 
    name,
    salary,
    department,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
  FROM employees
) ranked
WHERE dept_rank <= 3;

Running Totals with SUM()

-- Running total of sales
SELECT 
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Result:
-- order_date | amount | running_total
-- 2024-01-01 | 100    | 100
-- 2024-01-02 | 150    | 250  (100 + 150)
-- 2024-01-03 | 200    | 450  (100 + 150 + 200)

-- Running total per customer
SELECT 
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date
  ) AS customer_running_total
FROM orders;

-- Separate running total for each customer

LAG() and LEAD() - Previous/Next Row

-- Compare with previous day
SELECT 
  order_date,
  revenue,
  LAG(revenue) OVER (ORDER BY order_date) AS prev_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY order_date) AS daily_change
FROM daily_revenue;

-- Result:
-- order_date | revenue | prev_day_revenue | daily_change
-- 2024-01-01 | 1000    | NULL             | NULL
-- 2024-01-02 | 1200    | 1000             | 200
-- 2024-01-03 | 950     | 1200             | -250

-- LEAD() for next row
SELECT 
  order_date,
  revenue,
  LEAD(revenue) OVER (ORDER BY order_date) AS next_day_revenue
FROM daily_revenue;

-- LAG with offset (2 days ago)
SELECT 
  order_date,
  revenue,
  LAG(revenue, 2) OVER (ORDER BY order_date) AS two_days_ago
FROM daily_revenue;

🎯 Moving Averages

-- 7-day moving average
SELECT 
  order_date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day
FROM daily_revenue;

-- 3-month moving average
SELECT 
  month,
  total_sales,
  AVG(total_sales) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3month
FROM monthly_sales;

NTILE() - Divide into Buckets

-- Divide customers into 4 quartiles by spend
SELECT 
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customer_totals;

-- Result:
-- customer_id | total_spend | quartile
-- 101         | 50000       | 1  (top 25%)
-- 102         | 48000       | 1
-- 103         | 30000       | 2
-- 104         | 28000       | 2
-- 105         | 15000       | 3
-- 106         | 12000       | 3
-- 107         | 5000        | 4  (bottom 25%)
-- 108         | 3000        | 4

-- Use case: Segment customers
SELECT quartile, COUNT(*) as customer_count
FROM (
  SELECT NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
  FROM customer_totals
) quartiles
GROUP BY quartile;

✅ Real-World Examples

Pagination
-- Page 2, 10 items per page
SELECT *
FROM (
  SELECT 
    ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num,
    *
  FROM posts
) numbered
WHERE row_num BETWEEN 11 AND 20;
Top N Per Group
-- Top 5 products per category by sales
SELECT category, product_name, total_sales
FROM (
  SELECT 
    category,
    product_name,
    total_sales,
    ROW_NUMBER() OVER (
      PARTITION BY category 
      ORDER BY total_sales DESC
    ) AS rank
  FROM product_sales
) ranked
WHERE rank <= 5;
Percentage of Total
SELECT 
  product,
  sales,
  sales * 100.0 / SUM(sales) OVER () AS pct_of_total
FROM product_sales;

-- Result:
-- product | sales | pct_of_total
-- A       | 5000  | 50.0
-- B       | 3000  | 30.0
-- C       | 2000  | 20.0

💡 Window Function Syntax

 OVER (
  [PARTITION BY column]   -- Optional: Group by
  [ORDER BY column]       -- Optional: Sort order
  [ROWS/RANGE clause]     -- Optional: Frame specification
)

-- Common functions:
-- ROW_NUMBER(), RANK(), DENSE_RANK()
-- SUM(), AVG(), MIN(), MAX(), COUNT()
-- LAG(), LEAD()
-- FIRST_VALUE(), LAST_VALUE()
-- NTILE()

"Dashboard needed top 10 products per category. Was using subqueries and JOINs. Switched to window functions with ROW_NUMBER(). Query went from 2 seconds to 0.1 seconds. Code so much cleaner."

— Data Analyst

Related posts:

SQL: Use UNION ALL Instead of UNION When Duplicates Don't Matter

SQL: Use UNION ALL Instead of UNION When Duplicates Don't Matter

SQL Server: Fix a Foreign Key With No Supporting Index That Silently Turns Every Delete Into a Full ...

SQL Server: Fix a Foreign Key With No Supporting Index That Silently Turns Every Delete Into a Full ...

View the Definition of a Stored Procedure in SQL

View the Definition of a Stored Procedure in SQL

Views: 10

Post navigation

.NET Core: Use Background Services for Long-Running Tasks
SQL: Use CTEs for Readable Complex Queries

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (608)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (546)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com