How to filters data with two windows conditions? I want to get the result of two sub-queries. In my case I have users, user can have multiple access for doors, but sometimes doors are disabled so the access exist but do not point to any doors. I want to find the multiple access for the same user that have at least access with no door and at least one access with one door set.
Here is the SQL, that work. But it is very complicated. How could I improve it?
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS doors CASCADE;
DROP TABLE IF EXISTS access CASCADE;
CREATE TABLE users(id SERIAL PRIMARY KEY);
CREATE TABLE doors(id SERIAL PRIMARY KEY, type TEXT NOT NULL);
CREATE TABLE access(
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id),
  door_id INT, FOREIGN KEY (door_id) REFERENCES doors (id)
);
INSERT INTO users VALUES (1), (2), (3), (4);
INSERT INTO doors(type) VALUES ('front'), ('back');
INSERT INTO access(user_id, door_id)
VALUES
  (1, 1), -- user have only access to door
  (2, null), -- user with empty door access
  (3, 1), (3, null), -- user with access and empty access
  (4, 1),(4, 2) -- user with access to front and back access
  ;

--- We want all the door access for the same user with at least one empty access and one door access

WITH users_with_multiple_access AS (
  SELECT access.user_id 
  FROM access
  GROUP BY access.user_id
  HAVING (COUNT(access.user_id) &gt; 1)
),
users_with_multiple_access_and_without AS (
  SELECT access.*, CASE WHEN door_id IS NULL THEN 1 END AS marked 
  FROM access
  JOIN users_with_multiple_access USING(user_id)
),
users_with_multiple_access_marked AS (
  SELECT access.id AS access_id, sum(marked) OVER (PARTITION BY access.user_id) AS marked
  FROM access
  LEFT JOIN users_with_multiple_access_and_without uwmaw ON uwmaw.id = access.id
)
SELECT access.*
FROM access
JOIN users_with_multiple_access_marked awmam ON awmam.access_id = access.id AND marked IS NOT NULL;

Fiddle
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