📚[SQL] Subqueries and Window Functions in SQL

💡 Understanding Subqueries

A subquery, also known as an inner query or nested query, is a query within another SQL query.
It allows you perform complex queries by using the result of one query as a condition in another query.

Key Concepts of Subqueries

1. Type of Subqueries

• Single-Row Subquery : Returns a single row and is used with comparison operators such as '=', '>', '<'
  • Example :

  SELELCT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

• Multiple-Row Subquery : Returns multiple rows and is used with operators like 'IN', 'ANY', 'ALL'
  • Example :

  SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

• Correlated Subquery : A subquery that references columns from the outer query. It is evaluated once for each row processed by the outer query.
  • Example :

  SELECT e1.name, e1.salary FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

• Correlated Subquery : A subquery that references columns from the outer query. It is evaluated once for each row processed by the outer query.
  • Example :

2. Usage of Subqueries

• In 'SELECT' Statemnet:

SELECT name, (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id) AS max_salary FROM employees e;

• In 'WHERE' Clause:

SELECT name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE name = 'Sales');

• In 'FROM' Clause (Inline Views):

SELECT AVG(salary) FROM (SELECT salary FROM employees WHERE department_id = 10);

---

💡 Window Funtions

Window functions perform calculations across a set of table rows that are somehow related to current row. They are similar to aggregate funcions, but unlike aggregate functions, they do not cause rows to become grouped into a single output row,

Key Concpets of Window Functions

1. Types of Window Functions

• Aggregate Window Functions : Such as 'SUM()', 'AVG()', 'MAX(), 'MIN()', 'COUNT()'
• Ranking Window Functions: Such as 'ROW_NUMBER()', 'RANK()', 'DENSE_RANK()'
• Value Window Functions : Such as 'LEAD()', 'LAG()', 'FIRST_VALUE()', 'LAST_VALUE()'

2. Using Window Fuctions

• Basic Syntax:

SELECT column,
	window_function() OVER (PARTITION BY column1 ORDER-BY column2)
From table;

• Example with 'ROW_NUMBER()':

SELECT name, department_id, salary, 
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num 
FROM employees;

This query assigns a unique rank to each row within each department based on the salary.

• Example with 'SUM()':

SELECT department_id, salary, 
       SUM(salary) OVER (PARTITION BY department_id) AS total_salary 
FROM employees;

This query calculated the total salary for each department without collapsing the rows.

3. Frame Specification

• ROWS or RANGE : Specifies the window frame within the partition
  • Example:

  SELECT name, department_id, salary, 
       SUM(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
   From employees;

This query calculated a running total of salaries within each department