I have a unique customer id and a label that changes i.e.
| customer_id | label |
|---|---|
| 2 | A |
| 2 | A |
| 3 | B |
| 2 | C |
| 4 | A |
| 2 | A |
| 3 | C |
| 3 | B |
among other columns such as date
I'm using a groupby customer_id query that returns the min and max date i.e. I want to have the min date that a customer was in a given label and the last one. The issue is that if a customer returns to a previous label after a change then the min, max dates for this customer are wrong since the min date is the one of the first occurence and the max date is the one of the last occurence. I thought that a solution would be to count and label how many times a customer's label changes and then groupby customer_id and label. How can I do that in BigQuery or is there a better solution?
Thanks