Find employees who have the same salary as their manager.

To find employees who earn the exact same salary as their manager, you need to perform a Self-Join. This means you join the employees table to itself—treating one instance as the "Employee table" and the second instance as the "Manager table."

The Query

The join links the employee's manager_id to the manager's employee_id, and the WHERE clause filters for matching salary values:

sql

SELECT 
    e.employee_id AS emp_id,
    e.employee_name AS emp_name,
    e.salary AS emp_salary,
    m.employee_name AS manager_name,
    m.salary AS manager_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary = m.salary;

Use code with caution.

How It Works

• employees e: Represents the table context looking down at the regular employees.
• employees m: Represents the table context looking up at the managers.
• ON e.manager_id = m.employee_id: Establishes the relationship path between the employee and their boss.
• WHERE e.salary = m.salary: Isolates the records where both parties make the exact same amount of money.

---

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the logic live:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- 2. INSERT SAMPLE DATA
-- Top levels (No managers)
INSERT INTO employees (employee_id, employee_name, salary, manager_id) VALUES
(1, 'Fiona Gallagher', 120000.00, NULL), -- CEO
(2, 'Alice Smith', 95000.00, NULL);     -- VP

-- Reports
INSERT INTO employees (employee_id, employee_name, salary, manager_id) VALUES
(3, 'Bob Jones', 75000.00, 1),      -- Reports to Fiona (Diff Salary)
(4, 'Charlie Brown', 95000.00, 2),  -- Reports to Alice (SAME Salary - Matches!)
(5, 'Diana Prince', 95000.00, 2),   -- Reports to Alice (SAME Salary - Matches!)
(6, 'Evan Wright', 60000.00, 3);    -- Reports to Bob (Diff Salary)

-- 3. EXECUTE THE SELF-JOIN QUERY
SELECT 
    e.employee_name AS employee_name,
    e.salary AS employee_salary,
    m.employee_name AS manager_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary = m.salary;

Use code with caution.

Expected Query Output

employee_name	employee_salary	manager_name
Charlie Brown	95000.00	Alice Smith
Diana Prince	95000.00	Alice Smith

Write a query to find employees with salary greater than average salary in the entire company, ordered by salary descending.

To find employees whose salary is greater than the company-wide average, you cannot simply place WHERE salary > AVG(salary) in a standard query because aggregate functions are not allowed in the WHERE clause without grouping.

Instead, you must use a Subquery or a Window Function.

Here are the two best ways to write this query, followed by a complete playground script.

---

Method 1: Using a Subquery (Standard & Highly Compatible)

This method calculates the company-wide average first in an isolated subquery, then uses that single numeric value to filter the main table.

sql

SELECT 
    employee_id,
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Use code with caution.

Method 2: Using a Common Table Expression (CTE) & Window Function

This approach calculates the average alongside your other data columns using AVG() OVER(), allowing you to see exactly how much each employee exceeds the average.

sql

WITH SalaryAnalysis AS (
    SELECT 
        employee_id,
        employee_name,
        department,
        salary,
        AVG(salary) OVER() AS company_average
    FROM employees
)
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    ROUND(company_average, 2) AS company_average
FROM SalaryAnalysis
WHERE salary > company_average
ORDER BY salary DESC;

Use code with caution.

---

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the logic:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- 2. INSERT SAMPLE DATA
-- Total Salary = 430,000 | Average Salary = 86,000
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),  -- Above Average
(2, 'Bob Jones', 'Sales', 75000.00),            -- Below Average
(3, 'Diana Prince', 'Marketing', 82000.00),      -- Below Average
(4, 'Fiona Gallagher', 'Engineering', 110000.00),-- Above Average
(5, 'Hannah Abbott', 'Marketing', 68000.00);    -- Below Average

-- 3. EXECUTE FILTERED QUERY
SELECT 
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Use code with caution.

Expected Query Output

With a company average of 86,000.00, the query filters out the lower salaries and yields:

employee_name	department	salary
Fiona Gallagher	Engineering	110000.00
Alice Smith	Engineering	95000.00

Write a query to calculate the difference between current row and previous row's salary (lag function).

To calculate the difference between the current row's salary and the previous row's salary, you use the LAG() window function. LAG() allows you to look backward at a previous row's data within your result set without executing a complex self-join.

The Query

Here is the standard SQL query. It orders the employees by salary (highest to lowest) and computes the exact structural difference between adjacent positions:

sql

SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    -- Step 1: Fetch the previous row's salary
    LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS previous_salary,
    
    -- Step 2: Subtract the previous row's salary from the current row's salary
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_difference
FROM employees;

Use code with caution.

Syntax Deep Dive: LAG(column, offset, default)

• salary: The target column you want to retrieve a historical value from.
• 1 (Offset): How many rows to look backward. A value of 1 targets the immediate previous row.
• 0 or salary (Default Value): What to return if there is no previous row (e.g., the very first row in the dataset).
  • Passing 0 or omitting it helps show a blank baseline.
  • Passing the column itself (salary) ensures that subtracting it from the current row results in a clean 0 instead of a breaking NULL value on row one.

---

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the calculations live:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- 2. INSERT SAMPLE DATA
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),
(2, 'Bob Jones', 'Sales', 75000.00),
(3, 'Diana Prince', 'Marketing', 82000.00),
(4, 'Fiona Gallagher', 'Engineering', 110000.00),
(5, 'Hannah Abbott', 'Marketing', 68000.00);

-- 3. EXECUTE LAG QUERY
SELECT 
    employee_name,
    department,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS previous_employee_salary,
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_diff_from_previous
FROM employees;

Use code with caution.

Expected Query Output

employee_name	department	salary	previous_employee_salary	salary_diff_from_previous
Fiona Gallagher	Engineering	110000.00	NULL	0.00
Alice Smith	Engineering	95000.00	110000.00	-15000.00
Diana Prince	Marketing	82000.00	95000.00	-13000.00
Bob Jones	Sales	75000.00	82000.00	-7000.00
Hannah Abbott	Marketing	68000.00	75000.00	-7000.00

(Note: Fiona displays 0.00 difference because she is at the top of the chain; there is no previous record to subtract from her total.)