Window Functions & Analytical SQL

🪟 What is a Window Function?

Traditional GROUP BY clauses collapse individual table rows into single summary group rows, losing row-level granularity. In contrast, Window Functions compute aggregate values across a sliding window subset of rows (a partition) while preserving the individual row identity and original row count of the dataset.

• Analogy: Glass Floor Observation Deck in a Skyscraper.
  • GROUP BY: Demolishes individual room walls and converts an entire floor into one giant summary room.
  • Window Function (OVER()): Installs a transparent glass floor panel above the skyscraper rooms. As you walk across the panel, you observe individual rooms and calculate running totals or room salary ranks, but every room remains intact at its exact location.

                    GROUP BY vs. Window Functions
                                  │
    ┌─────────────────────────────┴─────────────────────────────┐
    ▼                                                           ▼
GROUP BY dept_id                                  AVG(salary) OVER(PARTITION BY dept_id)
Collapses 10 rows into 1 summary row             Preserves all 10 rows + appends aggregate column

SELECT emp_name, dept_id, salary,
       AVG(salary) OVER(PARTITION BY dept_id) AS dept_avg_salary,
       DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS sal_rank
FROM employees;

📐 Anatomy of a Window Function

A window function specification consists of three primary components:

FUNCTION() OVER (
    PARTITION BY dept_id         -- 1. Grouping Boundary
    ORDER BY salary DESC         -- 2. Evaluation Sort Order
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 3. Sliding Frame
)

📊 Ranking Functions: ROW_NUMBER vs RANK vs DENSE_RANK

When evaluating relative ranks within a partition (e.g., top 3 salespeople per region), SQL provides three ranking functions that handle tied values differently:

• ROW_NUMBER(): Assigns a strictly unique, sequential integer to every row regardless of ties.
• RANK(): Assigns identical rank numbers to tied rows, but leaves gaps in subsequent numbers (e.g., 1, 2, 2, 4).
• DENSE_RANK(): Assigns identical rank numbers to tied rows, but does not leave gaps (e.g., 1, 2, 2, 3).
• NTILE(n): Divides the ordered partition into $n$ equal-sized buckets and assigns bucket numbers (1 to $n$).

⏱️ Navigation & Offset Functions

Navigation functions access data from surrounding rows relative to the current row without performing expensive self-joins:

• LAG(col, offset, default): Accesses column data from a prior row in the partition (ideal for Month-over-Month growth calculations).
• LEAD(col, offset, default): Accesses column data from a subsequent row in the partition.
• FIRST_VALUE(col): Returns the first value in the window frame.
• LAST_VALUE(col): Returns the last value in the window frame.
• NTH_VALUE(col, n): Returns the $n$-th value in the window frame.

-- Calculate Month-over-Month (MoM) revenue growth
SELECT month_date,
       monthly_revenue,
       LAG(monthly_revenue, 1, 0) OVER (ORDER BY month_date) AS prior_month_revenue,
       monthly_revenue - LAG(monthly_revenue, 1, 0) OVER (ORDER BY month_date) AS mom_dollar_growth
FROM monthly_sales;

🖼️ Window Frame Specifications (ROWS vs RANGE)

Window frames define the exact subset of rows included in calculation relative to the current row:

                  Window Frame Boundary Specifications
                                    │
    ┌───────────────────────────────┴───────────────────────────────┐
    ▼                                                               ▼
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Evaluates fixed physical row count (3 rows)          Evaluates entire partition from first to last row

• ROWS: Operates on physical row counts relative to current row index.
• RANGE: Operates on logical values based on ORDER BY values (e.g., date ranges).

-- 3-Month Rolling Average Revenue
SELECT sale_date, revenue,
       AVG(revenue) OVER (
           ORDER BY sale_date 
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rolling_3m_avg
FROM daily_sales;

[!CAUTION] The LAST_VALUE() Default Frame Trap: When ORDER BY is included without an explicit frame, SQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Under this default frame, LAST_VALUE() will evaluate the current row as the last value instead of the end of the partition! Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when using LAST_VALUE().

❓ Conceptual Quizzes

💻 Practice Problems