Insights into Company
← SQL learning path

Window Functions

Learn SQL from fundamentals to advanced interview and production-level database concepts through practical examples, questions and quizzes.

Complete SQL Learning & Interview Path

Learn SQL by working with realistic databases instead of memorising isolated commands. Every concept can be connected with practical tables such as students, employees, departments, countries, states, customers, products and orders.

SQL Fundamentals SELECT WHERE JOINs GROUP BY Subqueries Indexes Transactions Window Functions Interview Questions
Start Here

SQL through a real database

SQL is used to store, retrieve, modify and analyse structured data. Instead of learning commands independently, imagine that we are building a small company database.

Student table

A student database can contain identity, name, course and marks.

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(100), course VARCHAR(100), marks INT );

Employee table

Employee information can be connected with departments, salaries and managers.

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department_id INT, salary DECIMAL(12,2) );

Country / State table

Geographic data is useful for understanding relationships and JOINs.

CREATE TABLE states ( id INT PRIMARY KEY, name VARCHAR(100), country_id INT );

Customer / Orders

E-commerce data provides practical examples for aggregation, JOINs and transactions.

CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, total DECIMAL(12,2), order_date DATE );
id name course marks
1 Rahul Java 85
2 Priya SQL 92
3 Amit React 76
SQL Learning Block 1

Questions 1 – 10

1
Question 41 of 10

What is a window function?

Solution

It calculates across related rows without collapsing them.
SELECT name,salary,AVG(salary) OVER() company_avg FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 42 of 10

Difference between RANK, DENSE_RANK and ROW_NUMBER?

Solution

  • ROW_NUMBER always increments.
  • RANK shares ranks for ties and leaves gaps.
  • DENSE_RANK shares ranks without gaps.
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 43 of 10

Find highest-paid employee in each department.

Solution

SELECT * FROM (SELECT e.*,ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) rn FROM employees e)x WHERE rn=1;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 44 of 10

Find top 3 employees from each department.

Solution

SELECT * FROM (SELECT e.*,ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) rn FROM employees e)x WHERE rn<=3;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 45 of 10

Find second-highest salary in every department.

Solution

SELECT * FROM (SELECT e.*,DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) rnk FROM employees e)x WHERE rnk=2;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 46 of 10

Calculate salary rank of every employee.

Solution

SELECT name,salary,RANK() OVER(ORDER BY salary DESC) salary_rank FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 47 of 10

Calculate department-wise salary rank.

Solution

SELECT name,department_id,salary,RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) dept_rank FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 48 of 10

Find running total of salaries.

Solution

SELECT name,salary,SUM(salary) OVER(ORDER BY id) running_total FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 49 of 10

Find running total of orders.

Solution

SELECT id,order_date,amount,SUM(amount) OVER(ORDER BY order_date,id) running_total FROM orders;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 50 of 10

Find previous employee salary.

Solution

SELECT name,salary,LAG(salary) OVER(ORDER BY salary DESC) previous_salary FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Knowledge Check

Quiz after Questions 1 – 10

Test yourself before moving to the next SQL learning block.

1. What is a window function?
2. Difference between RANK, DENSE_RANK and ROW_NUMBER?
3. Find highest-paid employee in each department.
4. Find top 3 employees from each department.
5. Find second-highest salary in every department.
6. Calculate salary rank of every employee.
7. Calculate department-wise salary rank.
8. Find running total of salaries.
9. Find running total of orders.
10. Find previous employee salary.