I'm using Postgresql and I'm struggling updating some values in a already created column with running count based on clientID.

The goal is to recognize when a client is 'NEW' (none of the previous values contains that customerID specifically) or if the customer is 'EXISTING' (there is at least one transaction before associated to that customer).

Here is an image of what I got now

![Here is an image of what I got now](https://i.stack.imgur.com/zpYyQ.png)

Here is an image of what I want to achieve

![Here is an image of what I want to achieve](https://i.stack.imgur.com/elYZs.png)

(customer or client are the same thing)

In my research I found that the use of sub-queries may cause long times of code execution, and I was looking how to use OVER and PARTITION BY clauses mixed with CASE, but still can't get a solution (all my errors are basically syntax error)

Sites visited but I failed the task anyway:

<https://www.sqlservercentral.com/articles/cumulative-sum-of-previous-rows>

[Running Count Total with PostgresQL](https://stackoverflow.com/questions/49077957/running-count-total-with-postgresql)
I look a lot of similar questions but I was unable to transform the SELECT query into the UPDATE statement that I need.