Show each employee salary alongside the average salary of their department.

Problem Statement

<p>Show each employee salary alongside the average salary of their department.</p>

Examples

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

Output: +---------+------------+--------+--------------------+ | name | department | salary | dept_avg | +---------+------------+--------+--------------------+ | Charlie | HR | 60000 | 67500.000000000000 | | Dana | HR | 75000 | 67500.000000000000 | | Alice | IT | 90000 | 85000.000000000000 | | Bob | IT | 80000 | 85000.000000000000 | +---------+------------+--------+--------------------+

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: Use a window function with OVER (...) to compute values across rows related to the current row without collapsing them like GROUP BY. 💡 Hint 2: Check if PARTITION BY is required to split windows by category or if only an ORDER BY inside OVER (...) is needed. 💡 Hint 3: Structure the query as: <window_func>() OVER (PARTITION BY ... ORDER BY ...) AS <alias>;

Editorial & Approach

Problem Overview & Intuition

To solve "PARTITION BY with Aggregate Window", we query the relational database engine using declarative SQL. The goal is to show each employee salary alongside the average salary of their 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 row projections to isolate the requested data.
Format & Order: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg FROM employees;

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.

PARTITION BY with Aggregate Window

Medium

Show each employee salary alongside the average salary of their department.

Example Scenarios

1Example 1

Input:

employees table

id	name	department	salary
1	Alice	IT	90000
2	Bob	IT	80000
3	Charlie	HR	60000
4	Dana	HR	75000

Output:

name	department	salary	dept_avg
Charlie	HR	60000	67500.000000000000
Dana	HR	75000	67500.000000000000
Alice	IT	90000	85000.000000000000
Bob	IT	80000	85000.000000000000

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.