Bits of .NET

📊 SQL Superpowers

Need row numbers? Running totals? Rankings? Window functions perform calculations across table rows without GROUP BY. Game-changing for analytics.

ROW_NUMBER() – Assign Row Numbers

-- Simple row number
SELECT 
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  name,
  salary,
  department
FROM employees;

-- Result:
-- row_num | name    | salary | department
-- 1       | Alice   | 120000 | Engineering
-- 2       | Bob     | 110000 | Engineering
-- 3       | Charlie | 100000 | Sales

-- Row number per department
SELECT 
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  name,
  salary,
  department
FROM employees;

-- Result (separate numbering per department):
-- dept_rank | name    | salary | department
-- 1         | Alice   | 120000 | Engineering
-- 2         | Bob     | 110000 | Engineering
-- 1         | Charlie | 100000 | Sales

📈 RANK() vs DENSE_RANK()

-- RANK() - Gaps in ranking for ties
SELECT 
  RANK() OVER (ORDER BY score DESC) AS rank,
  name,
  score
FROM students;
-- score: 100, 100, 95, 90
-- rank:  1,   1,   3,  4  (gap at 2!)

-- DENSE_RANK() - No gaps
SELECT 
  DENSE_RANK() OVER (ORDER BY score DESC) AS rank,
  name,
  score
FROM students;
-- score: 100, 100, 95, 90
-- rank:  1,   1,   2,  3  (no gap)

-- Top 3 salaries per department
SELECT *
FROM (
  SELECT 
    name,
    salary,
    department,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
  FROM employees
) ranked
WHERE dept_rank <= 3;

Running Totals with SUM()

-- Running total of sales
SELECT 
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Result:
-- order_date | amount | running_total
-- 2024-01-01 | 100    | 100
-- 2024-01-02 | 150    | 250  (100 + 150)
-- 2024-01-03 | 200    | 450  (100 + 150 + 200)

-- Running total per customer
SELECT 
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date
  ) AS customer_running_total
FROM orders;

LAG() and LEAD() - Previous/Next Row

-- Compare with previous day
SELECT 
  order_date,
  revenue,
  LAG(revenue) OVER (ORDER BY order_date) AS prev_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY order_date) AS daily_change
FROM daily_revenue;

-- Result:
-- order_date | revenue | prev_day_revenue | daily_change
-- 2024-01-01 | 1000    | NULL             | NULL
-- 2024-01-02 | 1200    | 1000             | 200
-- 2024-01-03 | 950     | 1200             | -250

-- LEAD() for next row
SELECT 
  order_date,
  revenue,
  LEAD(revenue) OVER (ORDER BY order_date) AS next_day_revenue
FROM daily_revenue;

💡 Real-World Examples

• Pagination: ROW_NUMBER() OVER (ORDER BY created_at) for server-side paging
• Top N per group: ROW_NUMBER() PARTITION BY category ORDER BY sales DESC
• Percentage of total: sales * 100.0 / SUM(sales) OVER ()
• Moving average: AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)