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:

and so on...

CodePudding user response：

You can use row_number():

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