Create a variable/column using count of events in a 13-week period from start date

The dataframe consists of case_number (chr), onboarding_date Y-m-d, and raised_at [Y-m-d h:m:s] (POSIXct) for each login event. I'm searching for a way to count how many login events (raised_at) occured between the onboarding date and 13-weeks (or 91 days) after onboarding.

log_df <- data.frame(
  case_number = c("000920", "000920", "000920", "000920", "000921", "000921"),
  onboarding_date = as.Date(c("2020-10-27", "2020-10-27", "2020-10-27", "2020-10-27", "2020-11-08", "2020-11-08")),
  raised_at = as.POSIXct(c("2020-10-27 02:52:31", "2020-10-27 05:40:51", "2020-10-27 05:46:25", "2020-10-28 09:05:21", "2020-11-08 23:39:15", "2020-11-11 00:19:16"))
)

I believe I need to create an interval object but I'm pretty new at this:

int <- interval(ymd("2001-01-01"), ymd("2002-01-01"))
interval = int_shift(int, duration(days = 91))

I'm not sure what to do next.

Can I now create a table that will only include raised_at events using onboarding_date+interval to exclude cases outside of my 91 day window of interest?
And then follow that up with a count of raised_at with group_by(case_number)?

I am getting stuck and confusing myself at the next part:

log_df %>%
  group_by(case_number) %>%
  mutate(interval = onboarding_date - min(onboarding_date) < 91,
         count_events = sum(raised_at) >= 1,
         event_within_interval = if_else(interval & count_events, 
                                         1, 0)) %>%
  ungroup()

It doesn't work and I can't seem to make it work.

Error in mutate():

> ! Problem while computing count_events = sum(raised_at) >= 1. i The
> error occurred in group 1: case_number = "ACU0000920". Caused by error
> in Summary.POSIXct(): ! 'sum' not defined for "POSIXt" objects

I understand that it cannot count the POSIXct dates but I don't know how to fix this error in my code to make it work. I'm sure I'm missing something obvious. If you are able to help me see what I am missing, I really appreciate your sage advice for someone just starting out.

You can measure the interval in days like this. There's no need to group at this stage, by the look of your data, as the onboarding_date appears to be constant by case_number. It's quicker not to group.

log_df  <- log_df |>
    mutate(
        interval_days = as.Date(raised_at) - onboarding_date,
        in_window = interval_days > 91
    )

log_df
#   case_number onboarding_date           raised_at interval_days in_window
# 1   1  000920      2020-10-27 2020-10-27 02:52:31        0 days     FALSE
# 2   2  000920      2020-10-27 2020-10-27 05:40:51        0 days     FALSE
# 3   3  000920      2020-10-27 2020-10-27 05:46:25        0 days     FALSE
# 4   4  000920      2020-10-27 2020-10-28 09:05:21        1 days     FALSE
# 5   5  000921      2020-11-08 2020-11-08 23:39:15        0 days     FALSE
# 6   6  000921      2020-11-08 2020-11-11 00:19:16        3 days     FALSE

Then to count how many are in each group simply:

log_df |>
    filter(in_window) |>
    group_by(case_number) |>
    summarise(n = n())

Your sample data doesn't have any cases where the interval is more than 91 days, but in your real data this should provide what you are asking for. I've used dplyr syntax as you're using it here. However, to do something similar in base R, you can do:

# This produces the same data.frame as above
log_df <- within(log_df, {
    interval_days <- as.Date(raised_at) - as.Date(onboarding_date)
    in_window <- interval_days > 91
})

# This produces a named array of counts outside the window for each `case_number` 
with(log_df, tapply(in_window, case_number, sum))

# Alternatively if you want a data.frame
aggregate(in_window ~ case_number, data = log_df, sum)

This should work!

log_df %>%
  mutate(events = map_dbl(onboarding_date, ~ sum(between(log_df$raised_at, .x, .x + days(91)))))