I have a dataset with and ID and a date looking like:

| ID |    date    |
| A  | 2021-07-20 |
| B  | 2021-07-20 |
| C  | 2021-07-20 |
| A  | 2021-07-20 |
| A  | 2021-07-21 |
| C  | 2021-07-21 |
| C  | 2021-07-21 |
| B  | 2021-07-22 |
| C  | 2021-07-22 |
| D  | 2021-07-22 |
| .  | ...        |

I have currently 123 distinct dates and few millions of ID.

Unfortunately, I am not allowed to export the ID (even if I anonymise it). I would like to know if there is a way to preprocess the data to be able to store and query the number of distinct ID on a date range.

For example between 2021-07-20 and 2021-07-21 there is only 3 distincts ID. I don't want to have 3 distinct on 2021-07-20 and 2 distinct on 2021-07-21 leading to 5 (I have added duplicated entries for this example but they are already dropped on my side).

I was thinking precomputing each ranges of days (123 * 123 / 2 combinations) but in term of scalability, it is not awesome (but better than nothing).

The other solution is to pivot the date in column and aggregate over all column with a count but I will have only groups of 1-2 individuals (because there is 2**123 combinations possibles of ady sequence per ID).

Any ideas is welcome and thanks for your support and time ;)



  • 2
    $\begingroup$ Will you need to add more data rows later on? $\endgroup$ – nir shahar Jul 21 at 17:19
  • $\begingroup$ For now, I am focusing on a fixed set of dates. If if can work for a daily update, that would be awesome but this is optionnal. $\endgroup$ – Nicolas M. Jul 21 at 18:21
  • $\begingroup$ I don't understand what's wrong with precomputing the answer for all ranges of dates. You say "in term of scalability, it is not awesome" but I don't understand what you mean by this. The time to look up the answer for a particular range sounds extremely fast. Can you be more precise? What are the requirement or the criteria by which you will evaluate answers? For instance, do you have specific performance metrics? $\endgroup$ – D.W. Jul 21 at 21:12
  • $\begingroup$ What I meant by scalability is for N days, I'll have to compute N^2/2 ranges (of course O(N^2 is still OK mainly with small number such as 120). Now on the implementation, for each ranges, I'll have to load the dataset, filter it and count the distinct ID (I am using pyspark and I don't know if there is a way to do it more efficiently than a for loop). $\endgroup$ – Nicolas M. Jul 22 at 6:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.