How can I apply a condition to multiple rows using SQL? CASE WHEN / PARTITION OVER

Question

I am trying to create a column that shows Yes or No based on a set of conditions. If the conditions are met, then 'Yes' would apply to every row in the group (even if the conditions are only met by some of the rows in the group).

This is what I have so far but it's yielding an error. In essence, if any of the rows belonging to the same shipment id has shipment name = 'CAL', I want the result to be 'Yes'.

CASE WHEN shipment.name = 'CAL' THEN 'Yes' ELSE 'No' OVER (PARTITION BY shipment.id) END AS fulfil

You can see my ideal table below

shipment.id	shipment.name	fulfil
1	CAL	Yes
1	NEV	Yes
2	PEN	No
2	NEV	No

Answer 1

You can use MAX() window function to return 'Yes' when there is a name with 'CAL'.
If there isn't, MAX() will return NULL which will be turned to 'No' by COALESCE():

SELECT id, name,
       COALESCE(MAX(CASE WHEN name = 'CAL' THEN 'Yes' END) OVER (PARTITION BY id), 'No') AS fulfil
FROM shipment;