PostgreSQL: Case with conditions based on two columns

Question

Imagine the dummy data

id   name     category    score   
1    Alex     A           11        
2    Alex     D           4      
3    Bill     A           81     
4    Bill     B           34       
5    Bill     D           2       
6    Carl     C           5       
7    Carl     D           10       

I would like to apply the action:

if score of A, B, or C > score of D
then 'Review'
else 'Pass'

So the output is:

id   name     category    score   conclusion
1    Alex     A           11       Review
2    Alex     D           4        Review
3    Bill     A           81       Review
4    Bill     B           34       Review
5    Bill     D           2        Review
6    Carl     C           5        Pass
7    Carl     D           10       Pass

how can I obtain this in PostgreSQL?

Answer 1

You want conditional aggregation with window functions:

select
  id, name, category, score,
  case when 
         max(score) filter (where category in ('A', 'B', 'C')) over (partition by name) >
         min(score) filter (where category = 'D')              over (partition by name)
       then 'Review'
       else 'Pass'
  end as result
from mytable
order by name, id;

If there is no A, B or C for a name or no D for a name, the result will be 'Pass'. If you want this differently, then you'll have to adjust the camparison.

The above query gets you a status per person shown in all their rows. If you want a different status per row instead, just compare with the row's score:

select
  id, name, category, score,
  case 
    when category = 'D' then null
    when score > min(score) filter (where category = 'D') over (partition by name) then 'Review'
    else 'Pass'
  end as result
from mytable
order by name, id;