Postgresql - Substracting two CASE Statements with 'AND' operator condition returns NULL when it should return a specific integer

lana222
October 28, 2024
150 views
0 votes
2 Answers

I’m doing this StrataScratch SQL question.

The suggested solution uses two subqueries for each max value of each department, and then it substracts them to get the absolute value. I wanted to solve this question using dense_rank/rank window function by ranking the salaries per department first and then ordering them that the highest salary will get number 1. I wrote this query where I did the above and put it into a CTE, then I intended to select the highest salary from the marketing dept by using a CASE statement and substract that with another case statement for the max value of the engineering dept.

WITH CTE AS
(
select 
    dpt.department,
    emp.salary,
    dense_rank() over (partition by department order by salary desc) as salary_rank
from db_employee as emp
join db_dept as dpt
on emp.department_id=dpt.id
where department = 'engineering' or department = 'marketing'
)
select
(case when department = 'marketing' AND salary_rank = 1 THEN salary END) -
(case when department = 'engineering' AND salary_rank = 1 THEN salary END) as sal_diff
FROM 
CTE

The problem is that a NULL value is returned. I tried understanding why is that, and I only saw that the value for the first case statement for the marketing dept is returning the value in the middle of the result page as if it returns a few null rows and then the desired salary (kinda weird, looks like a bug or something).

I mean this part:

(case when department = ‘marketing’ AND salary_rank=1 THEN salary END) = should return

While the below returns the desired salary value.
(case when department = ‘engineering’ AND salary_rank=1 THEN salary END) = should return 48187

I thought there may be an issue with either the placement of the ABS function but I can’t figure it out.
I know something must be up with my query (probably the select from cte query) so I’m hoping you could help. I really can’t figure it out. Is there a problem with my condition statement or my strategy? for some reason I thought this method with window functions would work.

Thanks in advance for your help 🙂

Answers

- Bohemian
- October 27, 2024 at 11:55 pm
- 0 votes
0
Your case conditions
```
(case when department = 'marketing' AND salary_rank = 1 THEN salary END) -
(case when department = 'engineering' AND salary_rank = 1 THEN salary END) as sal_diff
```
are impossible to both be true, so one will be null therefore the result of the subtraction must be be null.

Because department can’t be both ‘marketing’ and ‘engineering’, only one of the case statements matches and when there’s no else clause a null is returned.

You need to join the CTE to itself so both values are present in the one joined row:
```
WITH CTE AS (
  select 
    dpt.department,
    emp.salary,
    dense_rank() over (partition by department order by salary desc) as salary_rank
  from db_employee as emp
  join db_dept as dpt on emp.department_id=dpt.id
  where department in ('engineering', 'marketing')
)
select
  m.salary - e.salary as sal_diff
from CTE m
join CTE e on e.salary_rank = m.salary_rank
  and e.department = 'engineering'
where m.department = 'marketing'
and m.salary_rank = 1
```
Note also changing the condition to where department in ('engineering', 'marketing') rather than the OR which is easier to read, performs better if there’s an index on department and prevents boolean logic bugs if you introduce other conditions.
Login or Signup to reply.

- Charlieface
- October 28, 2024 at 2:04 am
- 0 votes
0
It seems to me that you are massively over-complicating this. You just need conditional aggregation, not window functions and CTEs.

Essentially you need to think of the query like this: after calculating the maximum salary of those two departments over the whole table, what is the difference between them? This immediately sends you to a normal aggregation query, not ranking functions.
```
select
  max(case when dpt.department = 'marketing'   THEN emp.salary END) -
  max(case when dpt.department = 'engineering' THEN emp.salary END) as sal_diff
from db_employee as emp
join db_dept as dpt on emp.department_id = dpt.id
where dpt.department in ('engineering', 'marketing');
```
Login or Signup to reply.