rolling count of distinct users

Using functions from dplyr and tidyr, for the 1-day window case:

have %>% 
  group_by(when) %>% 
  summarise(twoDayCount = n_distinct(user))

For larger windows:

window <- 2
have %>% 
  rowwise() %>% 
  mutate(when = list(when + lubridate::days(0:(window - 1)))) %>% 
  unnest(cols = when) %>%
  group_by(when) %>% 
  summarise(twoDayCount = n_distinct(user))

Note that this method will give you rows for a few later dates (in this case Jan 08), which you might want to remove.

If performance is an issue for larger datasets, here is a much faster (but slightly less elegant) solution:

window <- 2
seq.Date(min(have$when), max(have$when), by = "day") %>% 
  purrr::map(function(date) {
    have %>% 
        filter(when <= date, when >= date - days(window - 1))  %>%
        summarise(userCount = n_distinct(user)) %>%
        mutate(when = date)
    }) %>% 
  bind_rows()

It is probably a bit clumsy with the loop. But seems to work...

want <- data.frame(when = seq.Date(min(have$when), max(have$when), by = 1), 
                   twoDayCount = NA)

for (iDate in min(want$when):(max(want$when))) {
  dateWindow = c(iDate, iDate - 1)
  uniqueUsers = unique(have$user[have$when %in% dateWindow])
  want$twoDayCount[want$when == iDate] = length(uniqueUsers)
}

        when twoDayCount
1 2020-01-01           2
2 2020-01-02           3
3 2020-01-03           3
4 2020-01-04           3
5 2020-01-05           1
6 2020-01-06           2
7 2020-01-07           2

This probably will not port to dbplyr. But you can approach this using a tidyverse approach.

You first want to create a nested dataframe. 3 columns. First is date. Second are the users for that date, the second are users from the previous day (if available). You can then use purrr::map2 to apply a function to those data sets to find out how many unique users you have.

library(dplyr)
library(lubridate)
library(tidyr)
library(purrr)

# A function to get the number of distinct elements in a couple of dfs
num_distinct <- function(x,y){
  length(unique(c(x$user,y$user)))
}


df <- have %>% 
  distinct() %>% 
  group_by(when) %>% 
  nest() %>% 
  ungroup() %>% 
  inner_join(
    have %>% 
      distinct() %>% 
      group_by(when) %>% 
      nest()  %>% 
      ungroup() %>% 
      mutate(when = when + days(1)) %>% 
      rename(lag = data)
  ) 
  # calculate the rolling number of uniques
  df %>% 
  mutate(rolling = map2(data, lag, num_distinct)) %>% 
  select(-data, -lag) %>% 
  unnest(rolling)

This only shows results for dates with actual 2 day periods available, so may need to be modified depending on whether you what you want included.

A scalable solution for very large datasets would be to use data.table. In the example below I show how this would work if day was number of days since the start date.

library(tidyverse)
library(data.table)

window <- 30
dt <- tibble(day = seq(1:10000)) %>% 
  mutate(user = purrr::map(day, function(.) sample(1:10000, 10000, replace = TRUE))) %>% 
  unnest(user) %>% 
  as.data.table()

all_res <- list()

setkey(dt, day)

tracker <- 1
for(dd in unique(dt$day)){

  sub_dd <- dt[.(max(1,(dd-window)):dd)]

  all_res[[tracker]] <- tibble(day = dd, users = 
     length(unique(sub_dd[,user])))

  tracker <- tracker + 1

 }

all_res <- all_res %>% 
  bind_rows()

The key here is setting the key, which enables data.table to use binary search to speed up the filtering https://cran.r-project.org/web/packages/data.table/vignettes/datatable-keys-fast-subset.html.