Wondering how best to achieve this in SQL in BigQuery.
Table 1 has user\_id's and certain 'milestone dates'.
Table 2 has every day as date in a specific date range.

Based on the milestone dates of the user I want to evaluate the number of users in each 'phase' per day (out of table 2).
The check would simply be comparing the dates, where the date in table1 for every milestone is the 'phase transition' date. So the date for milestone2 is the date that user moved to phase2

So for instance:

```
Table 1
user_id | milestone1 | milestone2 | milestone3
1       | "2021-07-01" | "2021-07-03" | null
2       | "2021-07-02" | "2021-07-05" | "2021-07-06"
3       | "2021-07-02" | "2021-07-06" | null

```

The desired result is something like this:

```
Result
date           | phase1 | phase2 | phase3
"2021-07-01"   | 1      | 0      | 0
"2021-07-02"   | 3      | 0      | 0
"2021-07-03"   | 2      | 1      | 0  
"2021-07-04"   | 2      | 1      | 0 
"2021-07-05"   | 1      | 2      | 0
"2021-07-06"   | 0      | 2      | 1

```

I guess I could loop over the days in the date range and execute a query for each day in which I count the users in each step, but I'm wondering if there is a more efficient way to do this?