CODEWITHSUNDEEP
sqlamazon-redshift
user8834780
user8834780

Reputation: 1670

Partitioned exists clause in condition

I have the following query:

select user_id,
case when event='Analysis' 
and max(case when event='Strat' then max_time end) over(partition by user_id) >
max(case when event='Analysis' then max_time end) over(partition by user_id) then null 
**when event like 'Premium:%' and exists max(select distinct event from sub where event='Complete') over(partition by user_id) then null**
else event end event
from (
select distinct user_id, event, 
[*bunch of other columns*]
max(time) max_time
from table
[*bunch of joins and filters*]
group by 1,2
) sub

In the row with ** I am trying to add a clause such that: if event like 'Premium:%' but user has an event='Complete' (at any point) then change to event is null.

Any suggestions? Thank you

Upvotes: 0

Views: 280

Answers (1)

AlexYes
AlexYes

Reputation: 4208

you need to use a conditional sum in window function, partitioning by user id:

case 
    when event like 'Premium:%' 
    and sum(case when event='Complete' then 1 else 0 end) over (partition by user_id)>0 
    then null 
else event end

Upvotes: 1

Related Questions