Window Functions

By /

Window Functions perform calculations across a set of rows related to the current row — without collapsing the result set like GROUP BY does. They are extremely powerful for healthcare analytics, especially for running totals, rankings, and comparisons.

Basic Syntax

SELECT 
    column,
    Aggregate_Function(column) OVER (PARTITION BY column ORDER BY column) AS new_column
FROM table;

Key Window Functions

  • ROW_NUMBER() – Assigns unique sequential number
  • RANK() – Assigns rank with gaps
  • DENSE_RANK() – Assigns rank without gaps
  • LAG() / LEAD() – Access previous or next row
  • SUM() / AVG() / COUNT() with OVER – Running totals or moving averages

Healthcare Examples

1. Ranking Providers by Total Claims

SELECT 
    ProviderName,
    SUM(ClaimAmount) AS TotalClaims,
    RANK() OVER (ORDER BY SUM(ClaimAmount) DESC) AS ClaimRank
FROM Claims
GROUP BY ProviderName;

2. Running Total of Claims per Member

SELECT 
    MemberID,
    ClaimDate,
    ClaimAmount,
    SUM(ClaimAmount) OVER (PARTITION BY MemberID ORDER BY ClaimDate) AS RunningTotal
FROM Claims
ORDER BY MemberID, ClaimDate;

Key Takeaways

  • Window functions allow calculations without reducing the number of rows
  • PARTITION BY = GROUP BY within the window
  • Extremely useful for trends, rankings, and comparisons in healthcare data
  • Great for risk adjustment trend analysis and member journey tracking
Scroll to Top