SQL Quest › SQL Interview Questions › Subqueries & CTEs

Below Department Average

MediumFreeQuerying BasicsSubqueries & CTEsAggregation & Grouping

Google's compensation team runs a quarterly pay-equity scan: flag employees whose salary is below their own department's average. Show name, department, salary, and dept_avg_salary (rounded to nearest dollar). Order by department ascending, then salary ascending, then name ascending as a tie-breaker. The correlated-subquery pattern here is a core Google analyst interview test — it reveals whether you can write SQL that references the outer row from inside a subquery, which is the mental model most candidates trip on.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

employees

emp_id	name	department	position	salary	hire_date	manager_id	performance_rating
1	Alice Johnson	Engineering	Senior Developer	95000	2019-03-15	5	4.5
2	Bob Smith	Engineering	Developer	75000	2020-06-01	1	3.8
3	Carol Williams	Marketing	Marketing Manager	85000	2018-09-20	NULL	4.2

Expected output: Employees paid below their department average

Hint

Use a correlated scalar subquery: WHERE salary < (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department). Show the dept avg with a second correlated subquery in SELECT

Concepts

SELECT Subquery WHERE Aggregation

Practise the topic: SQL practice questions · CTE practice · GROUP BY exercises

In these company practice sets

Airbnb · Databricks · Google · Ramp · Snowflake · Goldman Sachs

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Below Department Average

Schema

Hint

Concepts

In these company practice sets

Related questions