Common Table Expressions (CTE): Named Queries
Advanced
The previous question solved "above their department's average" with a correlated subquery. A common table expression (CTE) makes the same logic easier to read by naming the intermediate step.
Write a query that first defines a CTE with each department's average salary, then returns name, department and salary of every employee earning more than the average of their department. The query must use a WITH clause.
Constraints
- Columns, in order:
name,department,salary - Define the department averages in a
WITHclause - Join the CTE back to
employees
Hints
Hint 1
WITH name AS (SELECT ...) SELECT ... FROM name defines a temporary named result.
Hint 2
Join the CTE to employees on department, then filter with WHERE.
Keywords
db
SQL
Run your code to see results
No results yet
Run your code to check it against the examples, or submit to run every test.