Above-Average Earners — Solution & ExplanationProblems / Above-Average Earners / EditorialApproachCorrelated Subquery (SQL)A correlated subquery references a column from the outer query:```sqlWHERE e.salary > (SELECT AVG(e2.salary)FROM employees e2WHERE e2.department = e.department -- correlation)```Conceptually, for each row in the outer query, the subquery runs with that row's department. In practice, the optimizer rewrites this as a hash join.Window Function (PySpark)AVG("salary").over(Window.partitionBy("department")) computes the department average on every row. Then a simple filter(salary > department_avg) selects above-average earners.Join Alternative (SQL)```sqlJOIN (SELECT department, AVG(salary) AS dept_avgFROM employees GROUP BY department) d ON e.department = d.departmentWHERE e.salary > d.dept_avg```All three approaches produce identical results. The correlated subquery is the most educational for understanding row-level comparisons.Hints

A correlated subquery references the outer query's row. Here, the inner query filters by the outer row's department.

The subquery executes once per outer row (conceptually). The optimizer may rewrite it as a join.

In PySpark, a window function AVG().over(partitionBy) is the natural equivalent of a correlated subquery.

Solve Above-Average Earners yourself →