SQL Window Functions for Data Analytics

This project is a project from Coursera to learn the window functions in SQL.

Retrieve all the data in the project’s db database to explore the tables content

SELECT * 
FROM employees;

SELECT * 
FROM departments;

SELECT * 
FROM regions;

SELECT * 
FROM customers;

SELECT * 
FROM sales;

Retrieve a list of employee_id, first_name, hire_date, and department of all employees ordered by the hire date

SELECT employee_id, 
       first_name, 
       department, 
       hire_date,
       ROW_NUMBER() OVER (ORDER BY hire_date) AS Row_N
FROM employees;

Retrieve the employee_id, first_name, hire_date of employees for different departments

SELECT employee_id, 
       first_name, 
       department, 
       hire_date,
       ROW_NUMBER() OVER (PARTITION BY department
ORDER BY hire_date) AS Row_N
FROM employees

Apply the RANK() function

SELECT first_name, 
       email, 
       department, 
       salary,
       RANK() OVER(PARTITION BY department
ORDER BY salary DESC) AS RANK_N
FROM employees

Apply the ROW_NUMBER()

SELECT first_name, 
       email, 
       department, 
       salary,
       ROW_NUMBER() OVER(PARTITION BY department
ORDER BY salary DESC)
FROM employees

Retrieve the hire_date. Return details of employees hired on or before 31st Dec, 2005 and are in First Aid, Movies and Computers departments

SELECT first_name, 
       email, 
       department, 
       salary, 
       hire_date
       RANK() OVER(PARTITION BY department ORDER BY salary DESC)
FROM employees
WHERE hire_date <= '2005-12-31' 
      AND department IN ('First Aid', 'Computers', 'Movies')

Return total number of employees in each department

SELECT department, 
       COUNT(*) dept_count
FROM employees
GROUP BY department
ORDER BY dept_count DESC;

Return the fifth ranked salary for each department

SELECT * 
FROM (
SELECT first_name, 
       email, 
       department, 
       salary,
       RANK() OVER(PARTITION BY departmentORDER BY salary DESC) AS RANK_N
FROM employees)
WHERE Rank_N = 5

Create a common table expression to retrieve the customer_id, and how many times the customer has purchased from the mall

WITH purchase_count AS (
SELECT customer_id, 
       COUNT(sales) AS purchase
FROM sales
GROUP BY customer_id
ORDER BY purchase DESC)

Run the complex query to understand the difference between ROW_NUMBER, RANK, DENSE_RANK

SELECT customer_id, 
       purchase,
       ROW_NUMBER() OVER (ORDER BY purchase DESC) AS Row_N,
       RANK() OVER (ORDER BY purchase DESC) AS Rank_N,
       DENSE_RANK() OVER (ORDER BY purchase DESC) AS Dense_Rank_N
FROM purchase_count
ORDER BY purchase DESC

Group the employees table into five groups based on the order of their salaries.

SELECT first_name, 
       department, 
       salary,
       NTILE(5) OVER(ORDER BY salary DESC)
FROM employees

Group the employees table into five groups for each department based on the order of their salaries

SELECT first_name, 
       email, 
       department, 
       salary,
       NTILE(5) OVER(PARTITION BY department
ORDER BY salary DESC)
FROM employees

Create a CTE that returns details of an employee and group the employees into five groups based on the order of their salaries

WITH salary_ranks AS (
SELECT first_name, 
       email, 
       department, 
       salary,
       NTILE(5) OVER(ORDER BY salary DESC) AS rank_of_salary
FROM employees)

Find the average salary for each group of employees

SELECT rank_of_salary, 
       ROUND(AVG(salary), 2) avg_salary
FROM salary_ranks
GROUP BY rank_of_salary
ORDER BY rank_of_salary

Retrieve the first names, department and number of employees working for each department

SELECT first_name, 
       department,
       (SELECT COUNT(*) AS dept_count 
        FROM employees e1 
        WHERE e1.department = e2.department)
FROM employees e2
GROUP BY department, first_name
ORDER BY department;

Total Salary for all employees

SELECT first_name, 
       department, 
       hire_date,
       SUM(salary) OVER(ORDER BY hire_date) as total_salary
FROM employees

Total Salary for each department

SELECT first_name, 
       department, 
       hire_date, 
       salary,
       SUM(salary) OVER(PARTITION BY department) as dept_total_salary
FROM employees

Total Salary for each department and order by the hire date

SELECT first_name, 
       hire_date, 
       department, 
       salary,
       SUM(salary) OVER(PARTITION BY department
ORDER BY hire_date) AS running_total
FROM employees;

Retrieve the different region ids

SELECT DISTINCT region_id
FROM employees;

Retrieve the first names, department and number of employees working in that department and region

SELECT first_name, 
       department,
       COUNT(*) OVER(PARTITION BY department) dept_count,
       region_id,
       COUNT(*) OVER(PARTITION BY region_id) region_count
FROM employees

Retrieve the first names, department and number of employees working in that department and in region 2

SELECT first_name, 
       department,
       COUNT(*) OVER(PARTITION BY department) AS dept_count
FROM employees
WHERE region_id = 2

Create a common table expression to retrieve the customer_id, ship_mode, and how many times the customer has purchased from the mall

WITH purchase_count AS (
SELECT customer_id, 
       ship_mode, 
       COUNT(sales) AS purchase
FROM sales
GROUP BY customer_id, ship_mode
ORDER BY purchase DESC)

Calculate the cumulative sum of customers purchase for the different ship mode

SELECT customer_id, 
       ship_mode, 
       purchase,
       SUM(purchase) OVER(PARTITION BY ship_mode
ORDER BY customer_id ASC) AS sum_of_sales
FROM purchase_count

Calculate the running total of salary. Retrieve the first_name, hire_date, salary of all employees ordered by the hire date

SELECT first_name, 
       hire_date, 
       salary,
       SUM(salary) OVER(ORDER BY hire_date 
RANGE BETWEEN UNBOUNDED PRECIDING AND CURRENT ROW) as running_total
FROM employees

Add the current row and previous row. Find the running average

SELECT first_name, 
       hire_date, 
       salary,
       SUM(salary) OVER(ORDER BY hire_date ROWS BETWEEN
       1 PRECEDING AND CURRENT ROW) AS running_total
FROM employees

Find the running average

SELECT first_name, 
       hire_date, 
       salary,
       AVG(salary) OVER(ORDER BY hire_date ROWS BETWEEN
				 2 PRECEDING AND CURRENT ROW) AS running_total
FROM employees

Review of the FIRST_VALUE() function

SELECT department, 
       division,
       FIRST_VALUE(department) OVER(ORDER BY department ASC) first_department
FROM departments

Create a common table expression to retrieve the customer_id, ship_mode, and how many times the customer has purchased from the mall.

WITH purchase_count AS (
SELECT customer_id, 
       COUNT(sales) AS purchase
FROM sales
GROUP BY customer_id
ORDER BY purchase DESC)