How to create new count column based on adjacent combinations in existing table

I have a simple table I’ve already built in BigQuery and all I want to do is what feels like a simple count of the number of times the combination of the person_id and the specific activity in the activity column has appeared in that table and create as a new column with a value/count of the adjacent combination in every row: ‘combination_count’ There are thousands of rows in the table so it’s no good creating a filter or wheres etc.

It feels really simple but it’s driving me mad. I’ve tried using counts and partitions but it doesn’t work for me.

desired result:

person_id	activity	combination_count
1234	activity_1	1
1234	activity_1	2
1234	activity_2	1
5678	activity_1	1

and so on…

Answer

You can use row_number():

select t.*,
       row_number() over (partition by person_id, activity order by person_id)
from t;