Definition
- Step 1:
groupingBy(dept, averagingDouble(salary))producesMapof department averages. - Step 2:
filter(e -> salary > avgMap.get(dept))keeps employees above their department's average. - Two passes required: you need all averages before you can filter individual employees.
- SQL equivalent:
WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept).
The National Bank of India HR manager wants to identify star performers: employees who earn more than the average of their own department. First she calculates each department's average. Then she checks each employee against their department's average.
Understanding the Concepts
What does this question ask?
Compare each employee's salary to the average of their department. This requires two passes: one to compute averages, one to filter.
How do you find employees above their department average?
package com.bank.hr;
import java.util.*;
import java.util.stream.Collectors;
public class AboveDeptAverage {
public static void main(String[] args) {
List<Employee> employees = Employee.getSampleEmployees();
// Step 1: compute average per department
Map<String, Double> avgByDept = employees.stream()
.collect(Collectors.groupingBy(
Employee::getDepartment,
Collectors.averagingDouble(
e -> e.getSalary().doubleValue())));
// Step 2: filter employees above their department's average
List<Employee> aboveAverage = employees.stream()
.filter(e -> e.getSalary().doubleValue()
> avgByDept.get(e.getDepartment()))
.toList();
aboveAverage.forEach(e ->
System.out.printf("%s (%s): Rs.%s (dept avg: Rs.%.0f)%n",
e.getName(), e.getDepartment(), e.getSalary(),
avgByDept.get(e.getDepartment())));
}
}
Rajesh (TRADING): Rs.1800000 (dept avg: Rs.1500000)
Deepak (RISK): Rs.1500000 (dept avg: Rs.1200000)
Suresh (OPERATIONS): Rs.1100000 (dept avg: Rs.900000)
Vikram (COMPLIANCE): Rs.1300000 (dept avg: Rs.1050000)
Can you do this in a single pass?
No. You need the department averages before you can filter individuals. The two-pass approach is the standard solution.
How do you group results by department?
Map<String, List<Employee>> aboveAvgByDept = employees.stream()
.filter(e -> e.getSalary().doubleValue()
> avgByDept.get(e.getDepartment()))
.collect(Collectors.groupingBy(Employee::getDepartment));
What are common mistakes?
Mistake: trying to compute the average and filter in one pipeline.
// WRONG: cannot reference avgByDept before it exists
List<Employee> wrong = employees.stream()
.filter(e -> e.getSalary().doubleValue() > ???) // average not yet known
.toList();
// CORRECT: two separate passes
Map<String, Double> avgByDept = employees.stream()
.collect(Collectors.groupingBy(dept, averagingDouble(salary)));
List<Employee> correct = employees.stream()
.filter(e -> e.getSalary().doubleValue() > avgByDept.get(e.getDepartment()))
.toList();
What about the SQL equivalent?
This is the stream translation of a correlated subquery:
SELECT * FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department = e1.department
);
The two-pass approach is the natural Java translation of this SQL pattern.
What about using BigDecimal for exact comparison?
Map<String, BigDecimal> exactAvg = employees.stream()
.collect(Collectors.groupingBy(
Employee::getDepartment,
Collectors.collectingAndThen(Collectors.toList(), list -> {
BigDecimal sum = list.stream()
.map(Employee::getSalary)
.reduce(BigDecimal.ZERO, BigDecimal::add);
return sum.divide(BigDecimal.valueOf(list.size()),
2, java.math.RoundingMode.HALF_UP);
})));
List<Employee> aboveExact = employees.stream()
.filter(e -> e.getSalary().compareTo(
exactAvg.get(e.getDepartment())) > 0)
.toList();
What are common interview follow-up questions?
- "How many employees are above average per department?" Group the filtered result and count.
- "What if a department has only one employee?" That employee's salary equals the average, so they are NOT above it (strictly greater).
- "How do you include employees AT the average?" Use
>=instead of>.
Visual Overview
Pass 1: Compute department averages ┌───────────────────────────────────┐ │ TRADING → avg Rs.1,500,000 │ │ RISK → avg Rs.1,200,000 │ │ OPERATIONS → avg Rs.900,000 │ │ COMPLIANCE → avg Rs.1,050,000 │ └───────────────────────────────────┘ Pass 2: Filter employees above their dept avg ┌───────────────────────────────────────────────┐ │ Rajesh TRADING 1800K > 1500K? YES → KEEP │ │ Priya TRADING 1200K > 1500K? NO → SKIP │ │ Deepak RISK 1500K > 1200K? YES → KEEP │ │ Suresh OPS 1100K > 900K? YES → KEEP │ │ Vikram COMP 1300K > 1050K? YES → KEEP │ └───────────────────────────────────────────────┘
Key Takeaway
Finding employees above their department's average requires two passes: first compute department averages with groupingBy and averagingDouble, then filter employees whose salary exceeds their department's average.
This cannot be done in a single stream pass because the averages must be known before individual comparisons can be made. The two-pass approach mirrors the SQL subquery pattern.
Remember one sentence: compute department averages first, then filter employees whose salary exceeds their department's average in a second pass.
---