You have 2 minutes to solve this SQL query.
Find the employee or employees who earn more than the average salary of their department and have been with the company for more than 5 years.
Assume the table structure:
employees(employee_id, employee_name, department, salary, joining_date)
Me: Challenge accepted!
SELECT
employee_id,
employee_name,
department,
salary,
joining_date
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
)
AND joining_date <= CURRENT_DATE - INTERVAL '5 years';
Explanation:
This query applies two conditions to identify experienced, high-performing employees.
• The correlated subquery calculates the average salary for each employee's department.
• The first condition returns employees earning above their department's average salary.
• The second condition filters employees who joined the company more than 5 years ago.
• Only employees satisfying both conditions are included in the final result.
This question tests your understanding of:
• Correlated Subqueries
• Aggregate Functions using AVG
• Date Arithmetic
• Multiple Filtering Conditions
Expected Output Example
Employee: John, Department: IT, Salary: 95,000, Joining Date: 2018-01-10
Employee: Sarah, Department: HR, Salary: 82,000, Joining Date: 2017-06-15
Alternative Using Window Functions
SELECT
employee_id,
employee_name,
department,
salary,
joining_date
FROM (
SELECT
*,
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
) e
WHERE salary > dept_avg_salary
AND joining_date <= CURRENT_DATE - INTERVAL '5 years';
This approach avoids a correlated subquery by calculating the departmental average once using a window function, which can be more efficient on large datasets.
Tip for SQL Job Seekers:
Real-world interview questions often combine multiple SQL concepts in a single problem. Practice writing queries that use:
• Window Functions
• Correlated Subqueries
• Date Functions
• Aggregate Functions
• Complex WHERE conditions
These combined-concept questions are common in mid-level and senior SQL interviews.
❤️ React with ❤️ for more SQL interview challenges!