SQL apply status to each row of account if condition is met

Question

I have the following dataset,

and would like to create a "flag" of sorts where any account with a bucket_type = 'Self-pay' and bucket_status = 'Outstanding' receives the description "Self-pay Outstanding" for all rows pertaining to that account. Other instances can be regarded as "N/A". Like so:

I'm somewhat familiar with row_count(), partition by, and case statements, but I'm not sure how I can combine them together to achieve what I want. I have the following code, and I'm getting tripped up on my case statement.

select 
account
,bucket_type
,bucket_status
,case when bucket_type = 'Self-pay' and bucket_status = 'Outstanding' over (partition by account)
then 'Self-pay Outstanding' else null end 
from account_table

Thanks much!

Answer 1

You could use a conditional window max():

select t.*,
    case max(case when bucket_type = 'Self-pay' and bucket_status = 'Outstanding' then 1 else 0 end) over(partition by account) 
        when 1 then 'Self-pay Outstanding'
        else 'NA'
    end as flag
from mytable t

An alternative is an exists condition with a correlated subquery:

select t.*,
    case when exists (select 1 from mytable t1 where t1.account = t.account and t1.bucket_type = 'Self-pay' and t1.bucket_status = 'Outstanding')
        then 'Self-pay Outstanding'
        else 'NA'
    end as flag
from mytable t

I can't really tell which solution would perform better. The window function is neater; the subquery would take advantage of an index on (account, bucket_type, bucket_status).