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 to Calculate Running Totals Without Self-Joins

- 03.02.26 - ErcanOPAK

Need running totals, rankings, or moving averages? Window functions do it in one query without complex self-joins or cursors.

The Old Painful Way – Self-Join:

-- Calculate running total of sales
SELECT 
    o1.OrderDate,
    o1.Amount,
    (SELECT SUM(o2.Amount) 
     FROM Orders o2 
     WHERE o2.OrderDate <= o1.OrderDate) AS RunningTotal
FROM Orders o1
ORDER BY o1.OrderDate;

-- Problems:
-- - Correlated subquery runs for EVERY row
-- - O(n²) performance (100K rows = 10 billion operations)
-- - Takes minutes on large tables

The Window Function Way:

-- Same result, 100x faster
SELECT 
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders
ORDER BY OrderDate;

-- Performance: O(n) instead of O(n²)
-- 100K rows: 2 seconds vs 3 minutes

How Window Functions Work:

-- Syntax:
-- FUNCTION() OVER (
--     PARTITION BY column   -- Optional: Group by
--     ORDER BY column       -- Optional: Order within partition
--     ROWS/RANGE clause     -- Optional: Frame specification
-- )

-- Without PARTITION BY: Operates on entire result set
-- With PARTITION BY: Resets calculation per group
-- ORDER BY: Determines calculation order

Running Total Examples:

-- Simple running total
SELECT 
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

-- Running total per customer
SELECT 
    CustomerId,
    OrderDate,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY CustomerId 
        ORDER BY OrderDate
    ) AS CustomerRunningTotal
FROM Orders;

-- Running average
SELECT 
    OrderDate,
    Amount,
    AVG(Amount) OVER (ORDER BY OrderDate) AS RunningAverage
FROM Orders;

Ranking Functions:

-- ROW_NUMBER: Unique sequential number
SELECT 
    ProductName,
    Sales,
    ROW_NUMBER() OVER (ORDER BY Sales DESC) AS RowNum
FROM Products;
-- Output: 1, 2, 3, 4, 5... (no ties)

-- RANK: Same rank for ties, skips numbers
SELECT 
    ProductName,
    Sales,
    RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM Products;
-- Output: 1, 2, 2, 4, 5... (ties get same rank, next rank skips)

-- DENSE_RANK: Same rank for ties, doesn't skip
SELECT 
    ProductName,
    Sales,
    DENSE_RANK() OVER (ORDER BY Sales DESC) AS DenseRank
FROM Products;
-- Output: 1, 2, 2, 3, 4... (ties get same rank, next rank doesn't skip)

-- Example with all three:
-- Sales: 100, 90, 90, 80, 70
-- ROW_NUMBER: 1, 2, 3, 4, 5
-- RANK: 1, 2, 2, 4, 5
-- DENSE_RANK: 1, 2, 2, 3, 4

Top N Per Group:

-- Get top 3 products per category
WITH RankedProducts AS (
    SELECT 
        CategoryId,
        ProductName,
        Sales,
        ROW_NUMBER() OVER (
            PARTITION BY CategoryId 
            ORDER BY Sales DESC
        ) AS Rank
    FROM Products
)
SELECT 
    CategoryId,
    ProductName,
    Sales
FROM RankedProducts
WHERE Rank <= 3
ORDER BY CategoryId, Rank;

-- Each category gets its top 3 products

Moving Average (Sliding Window):

-- 7-day moving average of sales
SELECT 
    OrderDate,
    Amount,
    AVG(Amount) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS MovingAvg7Day
FROM Orders;

-- Explanation:
-- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
-- = Current row + 6 rows before = 7 total rows
-- Calculates average of those 7 rows

-- 30-day moving average
SELECT 
    OrderDate,
    Amount,
    AVG(Amount) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS MovingAvg30Day
FROM Orders;

Lead and Lag (Access Previous/Next Row):

-- Compare current month to previous month
SELECT 
    Month,
    Revenue,
    LAG(Revenue) OVER (ORDER BY Month) AS PreviousMonthRevenue,
    Revenue - LAG(Revenue) OVER (ORDER BY Month) AS MonthOverMonthChange
FROM MonthlySales;

-- Get next row value
SELECT 
    OrderDate,
    Status,
    LEAD(Status) OVER (ORDER BY OrderDate) AS NextStatus
FROM Orders;

-- Access row 2 positions ahead
SELECT 
    Month,
    Revenue,
    LEAD(Revenue, 2) OVER (ORDER BY Month) AS TwoMonthsAhead
FROM MonthlySales;

First and Last Values:

-- First order per customer
SELECT 
    CustomerId,
    OrderDate,
    Amount,
    FIRST_VALUE(OrderDate) OVER (
        PARTITION BY CustomerId 
        ORDER BY OrderDate
    ) AS FirstOrderDate
FROM Orders;

-- Last order per customer
SELECT 
    CustomerId,
    OrderDate,
    Amount,
    LAST_VALUE(OrderDate) OVER (
        PARTITION BY CustomerId 
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastOrderDate
FROM Orders;

-- Note: LAST_VALUE needs frame specification
-- Otherwise it only sees rows up to current row

Percentage of Total:

-- Each product's percentage of total sales
SELECT 
    ProductName,
    Sales,
    CAST(Sales * 100.0 / SUM(Sales) OVER () AS DECIMAL(5,2)) AS PercentOfTotal
FROM Products;

-- Per category percentage
SELECT 
    CategoryId,
    ProductName,
    Sales,
    CAST(Sales * 100.0 / SUM(Sales) OVER (PARTITION BY CategoryId) AS DECIMAL(5,2)) AS PercentOfCategory
FROM Products;

Performance Tips:

-- ✅ Index columns in ORDER BY and PARTITION BY
CREATE INDEX IX_Orders_Date ON Orders(OrderDate);
CREATE INDEX IX_Orders_Customer_Date ON Orders(CustomerId, OrderDate);

-- ✅ Filter data before window function
SELECT 
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders
WHERE OrderDate >= '2024-01-01';  -- Filter first

-- ❌ Don't filter window function results unnecessarily
-- Window function must calculate all rows first

Related posts:

SQL: Use EXPLAIN to See Query Execution Plan and Find Bottlenecks

SQL: Use EXPLAIN to See Query Execution Plan and Find Bottlenecks

SQL The “Too Many Indexes” Problem — Why Over-Indexing Slows Everything

SQL The “Too Many Indexes” Problem — Why Over-Indexing Slows Everything

How to check for 'IS NOT NULL' And 'IS NOT EMPTY' string in SQL

How to check for 'IS NOT NULL' And 'IS NOT EMPTY' string in SQL

Views: 26

Post navigation

.NET Core: Use Minimal APIs to Create Lightweight Endpoints Without Controllers
SQL: Use MERGE to Upsert (Insert or Update) in One Statement

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 (981)
  • 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 (858)
  • 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 @ (609)
  • 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 (547)
  • 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 (981)
  • 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 (858)
  • 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