Question:I have a BigQuery table like this
I want to return the number of unique IDs by location and type of an arbitrary date range.
The issue I have is that there are multiple repeating lines covering similar dates, like the first two rows above.
For example, a date range of 2022-01-02 and 2022-01-03 would return
|location||type||count distinct ID|
I first tried creating a list of dates in like
ROW_NUMBER() OVER (PARTITION BY id,start_date,end_date)to expose only unique rows.
But I was only able to return the number of unique IDs for each day, rather than looking at the full date range as a whole.
I then tried joining to the same cte to return a unique row for each date, so something like
date | id | start_date | end_date | location | type
Where the columns from the first table above are duplicated for each date but this would require generating a huge number of rows to then further work with.
What is the correct way to acheive the desired result?
Answer:I think the simplest way is
If you have better answer, please add a comment about this, thank you!