Master SQL Window Functions in Minutes! ROW_NUMBER, RANK, NTILE, SUM & More Explained شرح بالعربي
LinkedIn: / mohamed-elsaeed54
code used:
CREATE TABLE sales (
id INT PRIMARY KEY,
employee_name VARCHAR(50),
department VARCHAR(50),
sale_amount DECIMAL(10,2),
sale_date DATE
);
-- ✅ INSERT SAMPLE DATA (including duplicate sale_amounts)
INSERT INTO sales (id, employee_name, department, sale_amount, sale_date) VALUES
(1, 'Alice', 'Electronics', 500, '2024-01-01'),
(2, 'Bob', 'Electronics', 700, '2024-01-02'),
(3, 'Eve', 'Electronics', 700, '2024-01-03'), -- same as Bob
(4, 'Tom', 'Electronics', 500, '2024-01-04'), -- same as Alice
(5, 'Nina', 'Electronics', 400, '2024-01-05'),
(6, 'Charlie', 'Clothing', 300, '2024-01-06'),
(7, 'David', 'Clothing', 600, '2024-01-07'),
(8, 'Frank', 'Clothing', 600, '2024-01-08'), -- same as David
(9, 'Grace', 'Clothing', 300, '2024-01-09'), -- same as Charlie
(10, 'Helen', 'Clothing', 200, '2024-01-10');
select * from sales
-- ======================================================
-- Group by example: Sum of sales per employee and department
-- ======================================================
SELECT
employee_name,
department,
SUM(sale_amount) AS dept_total_sales
FROM sales
GROUP BY employee_name, department;
SELECT
employee_name,
department,
sale_amount,
avg(sale_amount) OVER (PARTITION BY department) AS dept_total_sales
FROM sales;
-- ======================================================
-- 1️⃣ ROW_NUMBER(): Unique sequential number per partition
-- ======================================================
SELECT
employee_name,
sale_amount,
department,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS row_num
FROM sales;
-- ======================================================
-- 2️⃣ RANK(): Ranking with gaps if ties occur
-- ======================================================
SELECT
employee_name,
department,
sale_amount,
RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rank
FROM sales;
-- ======================================================
-- 3️⃣ DENSE_RANK(): Like RANK but no gaps in rank values
-- ======================================================
SELECT
employee_name,
department,
sale_amount,
DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS dense_rank
FROM sales;
-- ======================================================
-- 4️⃣ NTILE(n): Distribute rows into n approximately equal groups
-- ======================================================
SELECT
employee_name,
sale_amount,
NTILE(4) OVER (ORDER BY sale_amount DESC) AS tile
FROM sales;
-- ======================================================
-- 5️⃣ SUM() OVER(): Total sales per department
-- ======================================================
SELECT
employee_name,
department,
sale_amount,
SUM(sale_amount) OVER (PARTITION BY department) AS dept_total_sales
FROM sales;
SELECT
employee_name,
department,
sale_amount,
avg(sale_amount) OVER (PARTITION BY department) AS avg_by_dep,
min(sale_amount) OVER (PARTITION BY department) AS min_by_dep,
max(sale_amount) OVER (PARTITION BY department) AS max_by_dep
FROM sales;
-- ======================================================
-- 6️⃣ Running Total: Sum of sale_amount ordered by sale_date
-- Use Case: Track cumulative sales per employee over time
-- ======================================================
SELECT
sale_amount,
sale_date,
SUM(sale_amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;