How to filters data with two windows conditions?
Another way to do it would be:
select id, door_id, type
from (
    select u.id, a.door_id, d.type
         , count(case when a.door_id is null then 1 end) 
               over (partition by u.id) as x1
         , count(case when a.door_id is not null then 1 end) 
               over (partition by u.id) as x2
    from
    users u
    left join access a
        on a.user_id = u.id
    left join doors d
        on d.id = a.door_id
) as t
where x1 &gt; 0 
  and x2 &gt; 0;

I hijacked @McNets Fiddle and added this query to it