TECH **SQL Window Functions for Data Analysis: Zero-Fluff, Hands-On Guide**

SQL Window Functions for Data Analysis: Zero-Fluff, Hands-On Guide

(ROW_NUMBER, RANK, DENSE_RANK, NTILE)

1. What This Is & Why It Matters

Window functions let you analyze data across rows without collapsing them—unlike GROUP BY, which squashes rows into aggregates. Think of them as a periscope that lets you peek at neighboring rows while keeping the full dataset intact.

Why this matters in production:
- Ranking: "Show me the top 3 sales reps per region" (without GROUP BY destroying the detail).
- Time-series gaps: "Flag missing days in a daily sales log" (without self-joins).
- Running totals: "Calculate cumulative revenue by month" (without recursive CTEs).
- Percentiles: "Identify the 90th percentile of order values" (without manual sorting).

Real-world scenario:
You inherit a dashboard that shows "Top 5 products by revenue per store." The current query uses a GROUP BY + subquery for each store, running in 12 seconds. Rewrite it with RANK()—it drops to 0.8 seconds and scales to 10,000 stores.

2. Core Concepts & Components

3. Step-by-Step Hands-On: Ranking Sales Reps by Region

Prerequisites

• A SQL environment (PostgreSQL, BigQuery, Snowflake, or SQL Server).
• A table sales_reps with columns: rep_id, region, revenue, quarter.

Task

Write a query to: 1. Rank sales reps by revenue within each region.
2. Show only the top 2 reps per region.
3. Handle ties (e.g., two reps with $100K revenue).

Step 1: Inspect the Data

SELECT region, rep_id, revenue
FROM sales_reps
ORDER BY region, revenue DESC;

Expected output:

region   | rep_id | revenue
---------+--------+--------
East     | 101    | 150000
East     | 102    | 120000
East     | 103    | 120000  -- Tie
West     | 201    | 180000
West     | 202    | 160000
...

Step 2: Add RANK() with PARTITION BY

SELECT
  region,
  rep_id,
  revenue,
  RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank
FROM sales_reps;

Key:
- PARTITION BY region: Resets the rank for each region.
- ORDER BY revenue DESC: Ranks highest revenue first.

Output:

region | rep_id | revenue | rank
-------+--------+---------+------
East   | 101    | 150000  | 1
East   | 102    | 120000  | 2
East   | 103    | 120000  | 2  -- Tie
West   | 201    | 180000  | 1
West   | 202    | 160000  | 2

Step 3: Filter for Top 2 Reps

WITH ranked_reps AS (
  SELECT
    region,
    rep_id,
    revenue,
    RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank
  FROM sales_reps
)
SELECT region, rep_id, revenue, rank
FROM ranked_reps
WHERE rank <= 2;

Why this works:
- The CTE (ranked_reps) calculates ranks first.
- The outer query filters for rank <= 2.

Step 4: Compare RANK() vs. DENSE_RANK()

SELECT
  region,
  rep_id,
  revenue,
  RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rank
FROM sales_reps;

Output:

region | rep_id | revenue | rank | dense_rank
-------+--------+---------+------+-----------
East   | 101    | 150000  | 1    | 1
East   | 102    | 120000  | 2    | 2
East   | 103    | 120000  | 2    | 2
East   | 104    | 110000  | 4    | 3  -- Key difference!

Production insight:
- Use RANK() for "Top N" where gaps matter (e.g., "Top 3" should skip rank 4 if there’s a tie).
- Use DENSE_RANK() for consecutive numbering (e.g., customer tiers).

4. ? Production-Ready Best Practices

Performance

• Index PARTITION BY and ORDER BY columns: Window functions scan partitions sequentially. Indexes speed this up.
  sql CREATE INDEX idx_sales_reps_region_revenue ON sales_reps (region, revenue DESC);
• Avoid SELECT * with window functions: They process all rows, even if you only need a subset.
• Use QUALIFY (Snowflake/BigQuery) or CTEs: Filter window results early.
  sql -- Snowflake/BigQuery SELECT region, rep_id, revenue FROM sales_reps QUALIFY RANK() OVER (PARTITION BY region ORDER BY revenue DESC) <= 2;

Readability

• Name window functions clearly: sql RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS revenue_rank
• Use CTEs for complex logic: sql WITH daily_sales AS (
SELECT date, SUM(amount) AS total
FROM orders
GROUP BY date ), ranked_sales AS (
SELECT
date,
total,
RANK() OVER (ORDER BY total DESC) AS daily_rank
FROM daily_sales ) SELECT * FROM ranked_sales WHERE daily_rank <= 5;

Edge Cases

• Handle NULLs: Use COALESCE or NULLS LAST in ORDER BY.
  sql ORDER BY revenue DESC NULLS LAST
• Partition size: Large partitions (e.g., 1M rows) can slow queries. Consider pre-aggregating.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Question Patterns

1. "Top N per group":
2. Question: "Show the top 2 products by sales per category."
3. Trick: ROW_NUMBER() vs. RANK()—do ties matter?

4. Answer: Use RANK() if ties should share a rank.

5. Percentiles:

6. Question: "Identify the 90th percentile of order values."
7. Trick: NTILE(100) vs. PERCENT_RANK().

8. Answer: NTILE(100) for approximate buckets; PERCENT_RANK() for exact percentiles.

9. Running totals:

10. Question: "Calculate cumulative revenue by month."
11. Trick: Forgetting the frame clause (ROWS UNBOUNDED PRECEDING).
12. Answer:
    sql
SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING)

Key Distinctions

7. ? Hands-On Challenge

Challenge

You have a table employee_salaries with columns: department, employee_id, salary.
Write a query to: 1. Rank employees by salary within each department.
2. Show only employees in the top 25% of their department (use NTILE(4)).

Solution

WITH ranked_employees AS (
  SELECT
    department,
    employee_id,
    salary,
    NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_quartile
  FROM employee_salaries
)
SELECT department, employee_id, salary
FROM ranked_employees
WHERE salary_quartile = 1;

Why it works:
- NTILE(4) divides each department into 4 equal buckets (quartiles).
- salary_quartile = 1 filters for the top 25%.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL Window Functions Docs – Official guide with examples.
2. BigQuery Window Functions – Google’s syntax and optimizations.
3. Book: SQL for Data Analysis by O’Reilly – Chapter 5 covers window functions in depth.
4. Practice: LeetCode SQL Problems – Filter for "window function" tags.