CODEWITHSUNDEEP
sqlsql-serverquery-optimization
K_Learner
K_Learner

Reputation: 23

comparing efficieny of sql query - using windows function and a subquery

I want to understand if my query is correct and if it's efficient than 1st query

Write a query to display the employee id, first name and last name, SalaryDrawn, AvgCompare (salary - the average salary of all employees) and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.

SAMPLE employee table:

EMPLOYEE_ID | FIRST_NAME | LAST_NAME | SALARY | MANAGER_ID | DEPARTMENT_ID|

1st Query

SELECT  employee_id,  first_name, last_name,  salary AS SalaryDrawn,  
ROUND((salary -(SELECT AVG(salary) FROM employees)),2) AS AvgCompare,  
CASE  WHEN salary >= 
(SELECT AVG(salary) 
FROM employees) THEN 'HIGH'  
ELSE 'LOW'  
END AS SalaryStatus 
FROM employees;

My Answer using a CTE and windows function:

WITH cte AS 
(SELECT  employee_id,  first_name, last_name,  salary,
ROUND(AVG(Salary) OVER (),2) AS avgSal 
FROM employees)

Select employee_id, first_name, last_name, salary, ROUND((e.salary - avgSal ,2) AS AvgCompare,  
CASE  WHEN salary >= avgSal THEN 'HIGH'  
ELSE 'LOW'  
END AS SalaryStatus 
FROM cte

Upvotes: 0

Views: 85

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89266

I want to understand if my query is correct

Create some sample data, and the expected results. Run the query and compare.

and if it's efficient than 1st query

Look at the query plan and the resource utilization.

SET STATISTICS TIME ON

SET STATISTICS IO ON

Are an easy ways to see the query resource utilization.

Upvotes: 1

Related Questions