SQL Case statement

Question

Need SQL query help for the logic as below If at least 1 user under an account is "Enabled" then status = 'Active' If all users under an account are not "Enabled" then status = 'Cancelled' else 'Unknown'

create table usr (
  acctid varchar(1),
  usrid  varchar(5),
  sts  varchar(20)
  );

insert into usr values('A','U13','Enabled');
insert into usr values('A','U14','Enabled');
insert into usr values('A','U15','Disconnected');
insert into usr values('A','U16','Disconnected');
insert into usr values('B','U30','Disconnected');
insert into usr values('B','U31','Disconnected');
insert into usr values('C','U32',null);
insert into usr values('D','U133','Enabled');
insert into usr values('D','U12','Enabled');

Below is the expected result for status

Answer 1

You need in something like

SELECT *, 
       CASE WHEN COUNT(CASE WHEN sts = 'Enabled' THEN 1 END) OVER (PARTITION BY acctid) > 0 
            THEN 'Active'
            WHEN COUNT(CASE WHEN sts IS NOT NULL THEN 1 END) OVER (PARTITION BY acctid) > 0
            THEN 'Cancelled'
            ELSE 'Unknown'
            END Status
FROM usr

fiddle