In this post we are going to cover ranking window functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE. These are 7 real SQL interview questions, asked the way they come up on the job, and the last one is a scenario where a report silently loses a person because of the wrong ranking function.
Ranking questions are the most common window function questions in SQL interviews. Second highest salary, top N per group and latest row per customer all use the same few patterns. The interviewer is really checking one thing: do you know what happens on a tie?
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 5 of the SQL Server RealTime Questions Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: Two engineers earn the same salary. What is the difference between ROW_NUMBER, RANK and DENSE_RANK, and what does each one do on that tie?
- Question 2: HR asks for the second highest salary in the company. How do you write it, and what happens when there is no second salary?
- Question 3: HR now wants the third highest salary in each department. How do you write it, and why does filtering on RANK = 3 lose a department?
- Question 4: The sales director wants the top 2 products by revenue in each region. How do you write a top N per group query?
- Question 5: Customer service wants each customer's most recent order with all of its details. How do you get the latest row per customer?
- Question 6: Marketing wants customers split into four spending groups for a campaign. How do you do it with NTILE, and how are uneven groups handled?
- Scenario 7: The report shows the highest-paid employee in each department, but two people in Sales share the top salary and one is missing. Why is ROW_NUMBER = 1 the bug, and how do you fix it?
- The full SQL script
The short answer
ROW_NUMBER numbers rows uniquely, RANK gives ties the same number and leaves a gap, DENSE_RANK gives ties the same number with no gap. PARTITION BY restarts the numbering per group, and a CTE lets you filter on the rank: top N per group, Nth highest, latest row per key. When ties are real business results, ROW_NUMBER = 1 silently drops rows; use RANK or DENSE_RANK.
Keep that in mind. Every question below is one use of it.
Let's create some sample data
We use a small shop database called TB Shop: departments, employees, customers, products, orders, order items and payments. The script is written for PostgreSQL, and it runs on SQL Server, MySQL 8 or Snowflake with very small changes. Run it once and every query in this post works.
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);Here are the employees. Notice two things we will use later: Lina and Ravi in Engineering (department 20) both earn 90000, and Aisha, the CEO, has no department.
| emp_id | name | dept_id | manager_id | salary | hire_date |
|---|---|---|---|---|---|
| 1 | Aisha | NULL | NULL | 150000 | 2018-03-01 |
| 2 | Omar | 20 | 1 | 120000 | 2019-06-15 |
| 3 | Lina | 20 | 2 | 90000 | 2021-01-10 |
| 4 | Ravi | 20 | 2 | 90000 | 2022-04-05 |
| 5 | Mei | 20 | 2 | 75000 | 2024-09-01 |
| 6 | Sara | 10 | 1 | 95000 | 2020-02-20 |
| 7 | Daniel | 10 | 6 | 105000 | 2021-07-01 |
| 8 | Fatima | 10 | 6 | 60000 | 2023-11-12 |
| 9 | John | 30 | 1 | 70000 | 2019-09-30 |
| 10 | Zara | NULL | 9 | 55000 | 2026-01-05 |
Question 1: Two engineers earn the same salary. What is the difference between ROW_NUMBER, RANK and DENSE_RANK, and what does each one do on that tie?
All three number the rows in the order you give them in OVER (ORDER BY ...). The only difference is what they do on a tie.
Let's rank the four Engineering employees by salary, highest first, with all three functions side by side.
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE dept_id = 20
ORDER BY salary DESC, emp_id;| name | salary | row_num | rnk | dense_rnk |
|---|---|---|---|---|
| Omar | 120000 | 1 | 1 | 1 |
| Lina | 90000 | 2 | 2 | 2 |
| Ravi | 90000 | 3 | 2 | 2 |
| Mei | 75000 | 4 | 4 | 3 |
Lina and Ravi both earn 90000. ROW_NUMBER gives them 2 and 3, because it never repeats a number. RANK gives both of them 2, then skips to 4 for Mei. DENSE_RANK also gives both of them 2, but Mei gets 3, with no gap.
| On a tie | Numbers | Use it for | |
|---|---|---|---|
| ROW_NUMBER | different numbers | 1, 2, 3, 4 | dedup, latest row, exactly one |
| RANK | same number, then a gap | 1, 2, 2, 4 | competition-style ranking |
| DENSE_RANK | same number, no gap | 1, 2, 2, 3 | Nth highest value |
Look at the ROW_NUMBER line: we added emp_id as a tie-breaker. Without it, which of Lina or Ravi gets number 2 is not guaranteed, and it can change between runs.
Question 2: HR asks for the second highest salary in the company. How do you write it, and what happens when there is no second salary?
My first answer is DENSE_RANK. Rank the salaries from highest to lowest in a CTE, then keep rank 2.
WITH ranked AS (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT name, salary
FROM ranked
WHERE rnk = 2
ORDER BY name;| name | salary |
|---|---|
| Omar | 120000 |
The second highest salary is Omar's, 120000. Why DENSE_RANK and not ROW_NUMBER? If two people shared the top salary, ROW_NUMBER = 2 would return the top salary again. DENSE_RANK gives every distinct salary its own number, so rank 2 really is the second highest. Change the 2 to any N and you have the Nth highest salary.
Let's also try the classic alternative: the highest salary that is lower than the maximum.
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);| second_highest |
|---|
| 120000 |
Same answer, 120000. Now the edge case interviewers like: if there is no second salary, the MAX version still returns one row, with NULL, which is what many online tests expect. The DENSE_RANK version returns no rows at all.
Question 3: HR now wants the third highest salary in each department. How do you write it, and why does filtering on RANK = 3 lose a department?
Same idea, plus PARTITION BY dept_id, so the ranking restarts at 1 in every department. Let's calculate both RANK and DENSE_RANK so we can see the difference, and keep dense rank 3.
WITH ranked AS (
SELECT e.dept_id, e.name, e.salary,
RANK() OVER (PARTITION BY e.dept_id
ORDER BY e.salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY e.dept_id
ORDER BY e.salary DESC) AS dense_rnk
FROM employees e
)
SELECT d.dept_name, r.name, r.salary, r.rnk, r.dense_rnk
FROM ranked r
JOIN departments d ON d.dept_id = r.dept_id
WHERE r.dense_rnk = 3
ORDER BY d.dept_name, r.name;| dept_name | name | salary | rnk | dense_rnk |
|---|---|---|---|---|
| Engineering | Mei | 75000 | 4 | 3 |
| Sales | Fatima | 60000 | 3 | 3 |
Engineering returns Mei with 75000, and Sales returns Fatima with 60000. Now look at Mei's RANK: it is 4, not 3, because Lina and Ravi tie for second. If we had filtered on RANK = 3, Engineering would simply disappear from the report. That is why Nth highest questions use DENSE_RANK.
HR has only one employee, John, so there is no third salary and no row. If the business wants every department listed, start from departments and LEFT JOIN the ranked rows, with the rank filter in the ON clause, not in WHERE.
Question 4: The sales director wants the top 2 products by revenue in each region. How do you write a top N per group query?
This is the top N per group pattern, and it always has three steps:
- A CTE that calculates revenue per region and product, without cancelled orders. Ravi's region is NULL, so COALESCE labels it Unknown.
- A second CTE that ranks the products inside each region with ROW_NUMBER, highest revenue first.
- The outer query keeps the rows with a row number of 2 or less.
WITH product_revenue AS (
SELECT COALESCE(c.region, 'Unknown') AS region, p.product,
SUM(oi.qty * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY c.region, p.product
), ranked AS (
SELECT region, product, revenue,
ROW_NUMBER() OVER (PARTITION BY region
ORDER BY revenue DESC, product) AS rn
FROM product_revenue
)
SELECT region, product, revenue, rn
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;| region | product | revenue | rn |
|---|---|---|---|
| East | Laptop | 1200.00 | 1 |
| East | Chair | 750.00 | 2 |
| Unknown | Desk | 400.00 | 1 |
| West | Laptop | 1200.00 | 1 |
| West | Monitor | 300.00 | 2 |
East has Laptop with 1200.00, then Chair with 750.00. West has Laptop and Monitor. Unknown has only one product, the Desk. The ranking sits in its own CTE because you cannot filter on a window function in the WHERE of the same SELECT.
ROW_NUMBER returns at most 2 rows per region. If two products tie for second place and both should show, use RANK or DENSE_RANK, and accept that a region can return more than 2 rows.
Question 5: Customer service wants each customer's most recent order with all of its details. How do you get the latest row per customer?
ROW_NUMBER, partitioned by customer, ordered by order date descending. The newest order of every customer gets number 1, and we keep only those rows.
WITH ranked AS (
SELECT customer_id, order_id, order_date, status, amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders
)
SELECT c.name, r.order_id, r.order_date, r.status, r.amount
FROM ranked r
JOIN customers c ON c.customer_id = r.customer_id
WHERE r.rn = 1
ORDER BY c.name;| name | order_id | order_date | status | amount |
|---|---|---|---|---|
| Lina Park | 1011 | 2026-05-30 | Cancelled | 400.00 |
| Mei Chen | 1009 | 2026-04-22 | Delivered | 250.00 |
| Omar Ali | 1008 | 2026-04-01 | Delivered | 75.00 |
| Ravi Patel | 1007 | 2026-03-15 | Delivered | 400.00 |
| Sara K. | 1012 | 2026-06-11 | Delivered | 50.00 |
| Sara Khan | 1010 | 2026-05-09 | Shipped | 300.00 |
Six customers, six rows, and each one is the complete order: the ID, the date, the status and the amount. Chen Wu never ordered, so he is not in the list.
Why not GROUP BY customer and take MAX(order_date)? Because that gives you the date, not the rest of the row, and joining back on the date duplicates a customer who has two orders on the same day. Here order_id DESC is the tie-breaker, so every customer gets exactly one row.
Look at Lina Park: her latest order is Cancelled. If the business wants the latest valid order, filter the status inside the CTE. In Snowflake and Databricks, QUALIFY ROW_NUMBER() OVER (...) = 1 does the same thing without a CTE.
Question 6: Marketing wants customers split into four spending groups for a campaign. How do you do it with NTILE, and how are uneven groups handled?
First, a CTE with the total spent per customer. It starts from customers with a LEFT JOIN, and the cancelled filter sits in the ON clause, so Chen Wu, who never ordered, stays in with 0.00. Then NTILE(4), ordered by total spent, deals the customers into four groups.
WITH spend AS (
SELECT c.customer_id, c.name,
COALESCE(SUM(o.amount), 0.00) AS total_spent
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status <> 'Cancelled'
GROUP BY c.customer_id, c.name
)
SELECT name, total_spent,
NTILE(4) OVER (ORDER BY total_spent DESC,
customer_id) AS quartile
FROM spend
ORDER BY quartile, total_spent DESC;| name | total_spent | quartile |
|---|---|---|
| Sara Khan | 1550.00 | 1 |
| Lina Park | 1200.00 | 1 |
| Mei Chen | 1175.00 | 2 |
| Ravi Patel | 400.00 | 2 |
| Omar Ali | 375.00 | 3 |
| Sara K. | 50.00 | 3 |
| Chen Wu | 0.00 | 4 |
Seven customers into four groups does not divide evenly, so the first three groups get two customers each and the last group gets one. Sara Khan and Lina Park are the top group, and Chen Wu is alone in group 4.
NTILE splits by row count, not by value. Two customers with exactly the same total can land in different groups. If the business defines the groups by amount, for example over 1000, use a CASE expression instead.
Scenario 7: The report shows the highest-paid employee in each department, but two people in Sales share the top salary and one is missing. Why is ROW_NUMBER = 1 the bug, and how do you fix it?
Here is how this goes wrong in a real project. The report worked for months. Then HR hired Noah into Sales at 105000, exactly what Daniel earns. Let's add Noah and check the Sales department.
INSERT INTO employees
VALUES (11, 'Noah', 10, 6, 105000, '2026-03-02');The statement runs and changes the data. The check query below shows the result.
SELECT name, dept_id, salary
FROM employees
WHERE dept_id = 10
ORDER BY salary DESC, emp_id;| name | dept_id | salary |
|---|---|---|
| Daniel | 10 | 105000 |
| Noah | 10 | 105000 |
| Sara | 10 | 95000 |
| Fatima | 10 | 60000 |
Now run the report as it was written, with ROW_NUMBER = 1:
WITH ranked AS (
SELECT e.dept_id, e.name, e.salary,
ROW_NUMBER() OVER (
PARTITION BY e.dept_id
ORDER BY e.salary DESC, e.emp_id
) AS rn
FROM employees e
)
SELECT d.dept_name, r.name, r.salary
FROM ranked r
JOIN departments d ON d.dept_id = r.dept_id
WHERE r.rn = 1
ORDER BY d.dept_name, r.name;| dept_name | name | salary |
|---|---|---|
| Engineering | Omar | 120000 |
| HR | John | 70000 |
| Sales | Daniel | 105000 |
Sales shows only Daniel. ROW_NUMBER always returns exactly one row per department, so Noah is silently dropped, and nobody gets an error.
The fix is RANK instead of ROW_NUMBER, and we drop the emp_id tie-breaker, otherwise it splits the tie again.
WITH ranked AS (
SELECT e.dept_id, e.name, e.salary,
RANK() OVER (
PARTITION BY e.dept_id
ORDER BY e.salary DESC
) AS rnk
FROM employees e
)
SELECT d.dept_name, r.name, r.salary
FROM ranked r
JOIN departments d ON d.dept_id = r.dept_id
WHERE r.rnk = 1
ORDER BY d.dept_name, r.name;| dept_name | name | salary |
|---|---|---|
| Engineering | Omar | 120000 |
| HR | John | 70000 |
| Sales | Daniel | 105000 |
| Sales | Noah | 105000 |
Ties share rank 1, so Sales now returns both Daniel and Noah. For rank 1, DENSE_RANK gives the same rows.
- Use ROW_NUMBER when you need exactly one row: deduplication, the latest record.
- Use RANK or DENSE_RANK when ties are real business results.
- And ask the manager: if two people tie, should both show? That question is what makes it a senior answer.
Key points to remember
- ROW_NUMBER is unique; RANK leaves gaps; DENSE_RANK doesn't
- Nth highest: DENSE_RANK in a CTE, or MAX below MAX
- PARTITION BY restarts the ranking per group: top N per group
- ROW_NUMBER = 1: latest row per key; NTILE splits by row count
- When ties matter, ROW_NUMBER = 1 drops rows: use RANK
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order. Copy it and run it top to bottom.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- SQL Interview Q&A #5 - Ranking window functions
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16 (and cross-checked on DuckDB). Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA sql_q05; SET search_path TO sql_q05;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);
-- Step 1: ROW_NUMBER vs RANK vs DENSE_RANK on the salary tie
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE dept_id = 20
ORDER BY salary DESC, emp_id;
-- Step 2: Second highest salary with DENSE_RANK
WITH ranked AS (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT name, salary
FROM ranked
WHERE rnk = 2
ORDER BY name;
-- Step 3: Second highest salary: the classic MAX below MAX
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Step 4: Third highest salary in each department (PARTITION BY)
WITH ranked AS (
SELECT e.dept_id, e.name, e.salary,
RANK() OVER (PARTITION BY e.dept_id
ORDER BY e.salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY e.dept_id
ORDER BY e.salary DESC) AS dense_rnk
FROM employees e
)
SELECT d.dept_name, r.name, r.salary, r.rnk, r.dense_rnk
FROM ranked r
JOIN departments d ON d.dept_id = r.dept_id
WHERE r.dense_rnk = 3
ORDER BY d.dept_name, r.name;
-- Step 5: Top 2 products by revenue in each region
WITH product_revenue AS (
SELECT COALESCE(c.region, 'Unknown') AS region, p.product,
SUM(oi.qty * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY c.region, p.product
), ranked AS (
SELECT region, product, revenue,
ROW_NUMBER() OVER (PARTITION BY region
ORDER BY revenue DESC, product) AS rn
FROM product_revenue
)
SELECT region, product, revenue, rn
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;
-- Step 6: Latest order per customer (ROW_NUMBER = 1)
WITH ranked AS (
SELECT customer_id, order_id, order_date, status, amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders
)
SELECT c.name, r.order_id, r.order_date, r.status, r.amount
FROM ranked r
JOIN customers c ON c.customer_id = r.customer_id
WHERE r.rn = 1
ORDER BY c.name;
-- Step 7: NTILE: split customers into spending quartiles
WITH spend AS (
SELECT c.customer_id, c.name,
COALESCE(SUM(o.amount), 0.00) AS total_spent
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status <> 'Cancelled'
GROUP BY c.customer_id, c.name
)
SELECT name, total_spent,
NTILE(4) OVER (ORDER BY total_spent DESC,
customer_id) AS quartile
FROM spend
ORDER BY quartile, total_spent DESC;
-- Step 8: Scenario: HR hires Noah into Sales at the same salary as Daniel
INSERT INTO employees
VALUES (11, 'Noah', 10, 6, 105000, '2026-03-02');
-- check the result
SELECT name, dept_id, salary
FROM employees
WHERE dept_id = 10
ORDER BY salary DESC, emp_id;
-- Step 9: The bug: ROW_NUMBER = 1 silently drops a tied top earner
WITH ranked AS (
SELECT e.dept_id, e.name, e.salary,
ROW_NUMBER() OVER (
PARTITION BY e.dept_id
ORDER BY e.salary DESC, e.emp_id
) AS rn
FROM employees e
)
SELECT d.dept_name, r.name, r.salary
FROM ranked r
JOIN departments d ON d.dept_id = r.dept_id
WHERE r.rn = 1
ORDER BY d.dept_name, r.name;
-- Step 10: The fix: RANK = 1 keeps every tied top earner
WITH ranked AS (
SELECT e.dept_id, e.name, e.salary,
RANK() OVER (
PARTITION BY e.dept_id
ORDER BY e.salary DESC
) AS rnk
FROM employees e
)
SELECT d.dept_name, r.name, r.salary
FROM ranked r
JOIN departments d ON d.dept_id = r.dept_id
WHERE r.rnk = 1
ORDER BY d.dept_name, r.name;
Part 6: Running Totals, LAG and LEAD
The sample data on this page is invented for teaching. Every output table is the real result of the query above it, run on PostgreSQL 16 over that sample data, and the portable queries were also checked on a second engine. Where SQL Server syntax is different, the post says so.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.