Mastering Window Functions in SQL

When analyzing real-world medical data like the Gestational Diabetes Mellitus (GDM) dataset, we often need to segment patients, identify rankings, or assess their distribution across a metric. SQL window functions make this easy and efficient.

Let’s walk through how to use three common window functions — NTILE, RANK, and DENSE_RANK — with real examples. I am using GDM dataset to explain above 3 window functions.

1. NTILE(): Dividing Participants into Quartiles Based on Glucose Level

Suppose we want to segment all participants into 4 groups (quartiles) based on their BMI

• Uses:- NTILE helps stratify risk groups (e.g., highest quartile glucose).

  
  

2.RANK(): Ranking Based on ALT Change Percentage (Biomarkers Table)

Let’s say we want to rank participants by how much their ALT (alanine transaminase) levels changed — the higher the percentage, the higher the rank.

• Uses:- RANK can highlight outliers or priority cases.

  
  

3. DENSE_RANK(): Ranking Participants Based on MAP

Now, using visit 1 blood pressure values, we calculate MAP (Mean Arterial Pressure) and assign a dense rank — useful when multiple participants have the same MAP.

Uses:- DENSE_RANK is ideal when values tie and you want fair classification.

Now you guys must be thinking what is the difference between Dense Rank and Rank?If you guys look closely both output , Rank is not consecutive in Rank query while in Dense query it is consecutive. In short Rank skips the Rank when there is same Rank to any rows (e.g., 1, 2, 2, 4)while Dense rank gives consecutive numbers (e.g., 1, 2, 2,3) So it depends on the Situation when to use Rank or Dense Rank.

Window functions make your SQL smarter, more flexible, and analytics-ready — perfect for everything from business reports to healthcare insights.