SQL - Introduction to Window Function - Aggregate Function by HadiMaster

-- Create table for salary data
CREATE TABLE salary_data (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    job_title VARCHAR(100),
    salary INTEGER
);

-- Insert sample data into the salary_data table
INSERT INTO salary_data (employee_id, employee_name, job_title, salary) VALUES
(1, 'John Doe', 'Data Scientist', 90000),
(2, 'Jane Smith', 'Data Analyst', 65000),
(3, 'Bob Johnson', 'Machine Learning Engineer', 100000),
(4, 'Sarah Lee', 'Data Engineer', 75000),
(5, 'Michael Brown', 'Data Scientist', 95000),
(6, 'Emily Davis', 'Data Analyst', 60000),
(7, 'Alex Wilson', 'Machine Learning Engineer', 110000),
(8, 'Jessica Clark', 'Data Engineer', 70000),
(9, 'David Rodriguez', 'Data Scientist', 85000),
(10, 'Lisa Martinez', 'Data Analyst', 62000),
(11, 'James Taylor', 'Machine Learning Engineer', 105000),
(12, 'Olivia Anderson', 'Data Engineer', 72000),
(13, 'William Thomas', 'Data Scientist', 92000),
(14, 'Sophia White', 'Data Analyst', 63000),
(15, 'Ethan Hall', 'Machine Learning Engineer', 115000),
(16, 'Mia Moore', 'Data Engineer', 74000),
(17, 'Jacob Garcia', 'Data Scientist', 88000),
(18, 'Ava Martinez', 'Data Analyst', 61000),
(19, 'Benjamin Young', 'Machine Learning Engineer', 120000),
(20, 'Harper Hernandez', 'Data Engineer', 77000),
(21, 'Amelia King', 'Data Scientist', 89000),
(22, 'Ryan Adams', 'Data Analyst', 64000),
(23, 'Zoe Rivera', 'Machine Learning Engineer', 125000),
(24, 'Elijah Long', 'Data Engineer', 78000),
(25, 'Layla Torres', 'Data Scientist', 91000),
(26, 'Jackson Nguyen', 'Data Analyst', 63000),
(27, 'Penelope Scott', 'Machine Learning Engineer', 130000),
(28, 'Chloe Green', 'Data Engineer', 80000),
(29, 'Sebastian Lopez', 'Data Scientist', 93000),
(30, 'Aiden Hill', 'Data Analyst', 65000);

-- check data
SELECT * FROM salary_data

-- average salary for each job title
SELECT
    employee_id,
    employee_name,
    job_title,
    salary,
    AVG(salary) OVER(PARTITION BY job_title) AS avg_salary_per_job
FROM
    salary_data;

-- count of employee for each job title
SELECT
    employee_id,
    employee_name,
    job_title,
    salary,
    COUNT(*) OVER(PARTITION BY job_title) AS num_employees_per_job
FROM
    salary_data;

-- minimum salary for each job title
SELECT
    employee_id,
    employee_name,
    job_title,
    salary,
    MIN(salary) OVER(PARTITION BY job_title) AS min_salary_per_job
FROM
    salary_data;

-- maximum salary for each job title
SELECT
    employee_id,
    employee_name,
    job_title,
    salary,
    MAX(salary) OVER(PARTITION BY job_title) AS min_salary_per_job
FROM
    salary_data;

-- total salary for each job title
SELECT
    employee_id,
    employee_name,
    job_title,
    salary,
    SUM(salary) OVER(PARTITION BY job_title) AS total_salary_per_job
FROM
    salary_data;