Rolling Sum for number of questions solved for past 7 days by every user

We could first convert SOLVED_DATE to actual dates and then use map_dbl from purrr to find how many questions are solved in the last 7 days.

library(dplyr)
library(purrr)

df %>%
  mutate(SOLVED_DATE = as.Date(SOLVED_DATE)) %>%
  group_by(USER_ID) %>%
  mutate(Order_History = map_dbl(SOLVED_DATE, 
        ~sum(QUESTIONS_SOLVED[(SOLVED_DATE > . - 7) & (SOLVED_DATE < .)])))

#   USER_ID SOLVED_DATE QUESTIONS_SOLVED Order_History
#     <int> <date>                 <int>         <dbl>
# 1       1 2019-05-01                 1             0
# 2       1 2019-05-02                 1             1
# 3       2 2019-05-02                 1             0
# 4       2 2019-05-03                 1             1
# 5       2 2019-05-04                 1             2
# 6       2 2019-05-05                 1             3
# 7       1 2019-05-06                 1             2
# 8       2 2019-05-06                 1             4
# 9       1 2019-05-07                 1             3
#10       2 2019-05-07                 1             5
# … with 11 more rows

An alternative is using rollapply()from the zoopackage. I had to complete the sequence of dates however and then remove the additional rows afterwards. So Ronaks answer is probably more practical.

library(zoo)
library(tidyverse)

df %>%
  mutate(SOLVED_DATE = as.Date(SOLVED_DATE)) %>%
  group_by(USER_ID) %>%
  complete(SOLVED_DATE = full_seq(SOLVED_DATE, 1)) %>%
  mutate(PAST_7DAY_ORDER_HISTORY = rollapply(QUESTIONS_SOLVED, width = 7, align = "right", 
                                             FUN = function(x) sum(x[-1], na.rm = T), 
                                             fill = NA, partial = T)) %>%
  filter(!is.na(QUESTIONS_SOLVED))

It's nevertheless a nice function for this kind of work. You can set the size of your window via width =, tell it how to align the window.

Here's a non-equi join data.table solution. This relies on updating by reference.

library (data.table)
  dt[, SOLVED_DATE := as.Date(SOLVED_DATE)]
  dt[, `:=` (END_RANGE = SOLVED_DATE - 7, PAST_7DAY_SOLVED_HISTORY = 0L)]

  dt[dt
     , on = .(USER_ID
              , SOLVED_DATE > SOLVED_DATE
              , END_RANGE < SOLVED_DATE)
     , PAST_7DAY_SOLVED_HISTORY :=.N
     , by = .EACHI
     ][, END_RANGE := NULL]

Here's a dplyr solution in the same vein that uses some of @Ronak Shah's idea for the summation.

tib%>%
  mutate(SOLVED_DATE = as.Date(SOLVED_DATE))%>%
  inner_join(.,., by = 'USER_ID')%>%
  group_by(USER_ID, SOLVED_DATE = SOLVED_DATE.x)%>%
  summarize(
    QUESTIONS_SOLVED = first(QUESTIONS_SOLVED.x) #a little hacky
    , PAST_7DAY_SOLVED_HISTORY =sum(QUESTIONS_SOLVED.x[SOLVED_DATE.x > SOLVED_DATE.y & SOLVED_DATE.x - 7 < SOLVED_DATE.y]))

For this dataset, @Ronak is the fastest.

Unit: milliseconds
            expr    min      lq      mean median      uq     max neval
     purrr_ronak 2.0315 2.13435  2.306462 2.1988 2.32110  5.2616   100
 dplyr_cole_join 2.2900 2.38270  2.716720 2.4537 2.54155 17.7151   100
         dt_cole 4.4387 4.77680  5.102791 5.0109 5.16260  9.7652   100
  dplyr_zoo_hump 8.9480 9.31415 10.067683 9.4534 9.92390 40.9371   100