Query Help - Where Not Exists?

Question

I only want to return managers who have all active accounts. In this case I am looking to return jane and her three accounts and rows of data.

select *
from [Table1] t1 
left join [Table2] t2 
on t1.account = t2.account 
where lower(t1.flag)='y' 
and not exists (select 1 from [Table1] tt1 where tt1.account=t1.account and tti.flag in ('NULL','n'))

My excepted return needs to be: Expected Return Data

Answer 1

You can use window functions to collate the counts for each manager, then filter

select t.account, t.flag, t.manager from (
    select *, Count(*) over (partition by manager) cnt,
      Sum(case when flag='y' then 1 end) over(partition by manager) f
    from t
)t
where cnt=f