I have a dataset in the below structure:
| Id | startDate | endDate |
I want to know how many id’s are exist within each date in the time range.
so the result should appear as below-
| Date | idCount |
is there any aggregation function that enables do preform this kind of calculation?
do I have to make a data preparation of joining time dimension of all relevant dates with the original dataset where dim_time.date between startDate and endDate?