Find employees who earn more than the average salary of their department. Show name, salary, and department.

Problem Statement

<p>Find <code>employees</code> who earn more than the average salary of their department. Show name, salary, and department.</p>

Examples

Input: employees table: +----+---------+------------+--------+ | id | name | department | salary | +----+---------+------------+--------+ | 1 | Alice | IT | 90000 | | 2 | Bob | IT | 60000 | | 3 | Charlie | IT | 120000 | | 4 | Dana | HR | 50000 | | 5 | Eve | HR | 80000 | +----+---------+------------+--------+

Output: +---------+------------+--------+ | name | department | salary | +---------+------------+--------+ | Charlie | IT | 120000 | | Eve | HR | 80000 | +---------+------------+--------+

Explanation: The records are grouped by category and the aggregate calculation is applied to produce the summary result.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Identify the filtering conditions needed to isolate the target rows from the table. 💡 Hint 2: Use appropriate operators: = for exact matches, LIKE for wildcard patterns, IN for sets, or BETWEEN for numeric/date ranges. 💡 Hint 3: Combine filters with AND/OR if multiple conditions exist: SELECT <cols> FROM <table> WHERE <condition>;

Editorial & Approach

Problem Overview & Intuition

To solve "Employees Earning Above Department Average", we query the relational database engine using declarative SQL. The goal is to find employees who earn more than the average salary of their department. show name, salary, and department. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply WHERE filtering to isolate the requested data.
Format & Order: Sort the resulting records according to specified order criteria.

Optimal Implementation (SQL)

SELECT name, department, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department) ORDER BY name;

Complexity Analysis

Time Complexity O(N log N) for sorting or partitioning rows.

Space Complexity O(N) for intermediate group hash tables or window buffers.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

Employees Earning Above Department Average

Hard

Find employees who earn more than the average salary of their department. Show name, salary, and department.

Example Scenarios

1Example 1

Input:

employees table

id	name	department	salary
1	Alice	IT	90000
2	Bob	IT	60000
3	Charlie	IT	120000
4	Dana	HR	50000
5	Eve	HR	80000

Output:

name	department	salary
Charlie	IT	120000
Eve	HR	80000

Explanation:

The records are grouped by category and the aggregate calculation is applied to produce the summary result.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.