number of appearances of an atribute before a date in a query

I have this table

and what I need as an output is to have for each payment the number of payments that have been done with the same credit card.

For example,

for the first payment there would be 0 payments with the same credit card. for the second payment there would be 1 payment with the same credit card. for the fifth payment there would be 1 payment with the same credit card.

Does anyone have an idea? thanks

Answer

SELECT *, 
       SUM(status = 'approved') OVER (PARTITION BY credit_card_hash 
                                      ORDER BY creation_date) AS payments_with_this_card
FROM sourcetable

If current record must NOT be counted then substract 1. Or specify window boundaries BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING.