PostgreSql - How to create conditional column with the filter on another column?

Question

I want to add 1 more columns where segment out whether the customer had sold at least one product or not. Data example:

ProductID    Customer     Status
1            John         Not sold
2            John         Not Sold
3            John         Sold

My expect result

ProductID    Customer     Status         Sold_at_least_1
1            John         Not sold       Yes
2            John         Not Sold       Yes
3            John         Sold           Yes
4            Andrew       Not Sold       No
5            Andrew       Not Sold       No
6            Brandon      Sold           Yes       

This is an example data. Sorry for any inconvenience as I unable to extract data out. Btw, appreciating for any helps.

Answer 1

You can do a window count of records of the same customer that have status = 'Sold' in a case expression:

select 
    t.*, 
    case when sum( (status = 'Sold')::int ) over(partition by customer) >= 1
        then 'Yes'
        else 'No'
    end
from mytable

NB: note that this does not magically create new records (as shown in your sample data). This query gives you as many records in the resultset as there are in the table, with an additionnal column that indicates whether each cutsomer has at least one sold item in the table.

Here is a demo provided by VBokšić (thanks).