SQL retrieve columns based on two rows values conditions

Question

I have a table called products

    id | product_id     | detail       | value
   ----+----------------+--------------+--------
   1   | 1              | size         | medium
   2   | 1              | load         | yes
   5   | 1              | color        | green
   3   | 1              | availability | 10
   4   | 1              | deliverable  | yes
   5   | 2              | size         | small
   6   | 2              | load         | no
   7   | 2              | color        | bleu
   8   | 2              | availability | 5
   9   | 2              | deliverable  | yes

I need to develop a query where I can extract rows based on a condition.

• I want to select id, product_id, detail,value

• if detail="load" and value="yes" then select * execept detail="delivrable" and I do not care its value

• if detail="load" and value="no" then select * execept detail="load" and I do not care its value => I need only 1 detail between load and deliverable based on the values detail="load" and value="yes" as shown in the wished outcomes table below

   id  | product_id     | detail       | value
   ----+----------------+--------------+-------
   1   | 1              | size         | medium
   2   | 1              | load         | yes
   5   | 1              | color        | green
   3   | 1              | availability | 10
   5   | 2              | size         | small
   7   | 2              | color        | bleu
   8   | 2              | availability | 5
   9   | 2              | deliverable  | yes
  

Thank you in advance for your help

Answer 1

Consider below

select * except(a,b,c,d) from (
  select *, 
    countif(detail="load" and value="yes") over win1 > 0 as a,
    countif(detail="deliverable") over win2 = 0 as b,  
    countif(detail="load" and value="no") over win1 > 0 as c,
    countif(detail="load") over win2 = 0 as d  
  from your_table
  window win1 as (partition by product_id),
  win2 as (win1 order by id rows between current row and current row) 
)
where (a and b) or (c and d)             

if applied to sample data in your question - output is