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