Counting users based several conditions with filtering based on two rows

user_13044702 0
Viewed 70

I am trying to find an R solution for https://platform.stratascratch.com/coding/514-marketing-campaign-success-advanced?python=

And am coming up short regarding a rather annoying edge-case.

The idea is to count unique user who made purchases that follow these rules:

No same day purchases only

user 1 makes purchases on 2019-01-01 and 2019-01-02 - OK
user 2 makes purchase 2019-01-01 and 2019-01-01 - NOT OK
user 3 makes purchase 2019-01-01 and 2019-01-01 and 2019-01-02 - OK

this is independent on what was purchased.

No repeat purchases of first day purchase only

user 4 makes purchase of A on 2019-01-01 and B on 2019-01-02 - OK
user 5 makes purchase of A on 2019-01-01 and A on 2019-01-02 - NOT OK
user 6 makes purchase of A on 2019-01-01 and B on 2019-01-01 and A on 2019-01-02 and B on 2019-01-03 - NOT OK
user 7 makes purchase of A on 2019-01-01 and A on 2019-01-02 and B on 2019-01-02 - OK

for user 7, making a similar purchase does not exclude you as long as a different purchase is made.

structure(list(user_id = c(10L, 10L, 10L, 11L, 11L, 12L, 12L, 
13L, 13L, 14L, 14L, 14L, 15L, 15L, 15L, 16L, 16L, 17L, 17L, 18L, 
18L, 19L, 20L, 21L, 21L, 22L, 22L, 23L, 24L, 25L, 25L, 25L, 25L, 
26L, 27L, 28L, 29L, 30L, 31L, 32L, 33L, 34L, 35L, 36L, 37L, 38L, 
39L, 40L, 41L, 42L, 43L, 43L, 44L, 44L, 45L, 45L, 46L, 46L, 46L, 
46L, 46L, 47L, 47L, 48L, 48L, 49L, 49L, 49L, 49L, 50L, 50L, 50L, 
50L, 51L, 51L, 52L, 52L, 53L, 53L, 54L, 54L, 55L, 55L, 56L, 56L, 
57L, 57L, 57L, 57L, 58L, 58L, 58L, 58L, 59L, 60L, 61L, 62L, 63L, 
64L, 65L, 66L, 67L), created_at = c("2019-01-01", "2019-01-02", 
"2019-03-31", "2019-01-02", "2019-03-31", "2019-01-02", "2019-03-31", 
"2019-01-05", "2019-03-31", "2019-01-06", "2019-01-06", "2019-03-31", 
"2019-01-08", "2019-01-09", "2019-03-31", "2019-01-10", "2019-03-31", 
"2019-01-11", "2019-03-31", "2019-01-12", "2019-01-12", "2019-01-12", 
"2019-01-15", "2019-01-16", "2019-01-17", "2019-01-18", "2019-01-19", 
"2019-01-20", "2019-01-21", "2019-01-22", "2019-01-22", "2019-01-24", 
"2019-01-27", "2019-01-25", "2019-01-26", "2019-01-27", "2019-01-27", 
"2019-01-29", "2019-01-30", "2019-01-31", "2019-01-31", "2019-01-31", 
"2019-02-03", "2019-02-04", "2019-02-05", "2019-02-06", "2019-02-07", 
"2019-02-08", "2019-02-08", "2019-02-10", "2019-02-11", "2019-03-05", 
"2019-02-12", "2019-03-05", "2019-02-13", "2019-03-05", "2019-02-14", 
"2019-02-14", "2019-03-09", "2019-03-10", "2019-03-11", "2019-02-14", 
"2019-03-11", "2019-02-14", "2019-03-12", "2019-02-18", "2019-02-18", 
"2019-02-18", "2019-02-18", "2019-02-20", "2019-02-21", "2019-03-13", 
"2019-03-14", "2019-02-21", "2019-03-13", "2019-02-23", "2019-03-18", 
"2019-02-24", "2019-03-19", "2019-02-25", "2019-03-20", "2019-02-26", 
"2019-03-20", "2019-02-27", "2019-03-20", "2019-02-28", "2019-02-28", 
"2019-03-20", "2019-03-20", "2019-02-28", "2019-03-01", "2019-03-02", 
"2019-03-25", "2019-03-04", "2019-03-05", "2019-03-26", "2019-03-27", 
"2019-03-27", "2019-03-27", "2019-03-27", "2019-03-31", "2019-03-31"
), product_id = c(101L, 119L, 111L, 105L, 120L, 112L, 110L, 113L, 
118L, 109L, 107L, 112L, 105L, 110L, 116L, 113L, 107L, 116L, 104L, 
114L, 113L, 114L, 117L, 105L, 114L, 113L, 118L, 119L, 114L, 114L, 
115L, 114L, 115L, 115L, 104L, 101L, 111L, 111L, 104L, 117L, 117L, 
110L, 117L, 102L, 102L, 113L, 120L, 115L, 114L, 105L, 102L, 104L, 
105L, 102L, 119L, 105L, 102L, 102L, 102L, 103L, 103L, 110L, 105L, 
115L, 105L, 106L, 114L, 112L, 116L, 118L, 118L, 118L, 118L, 120L, 
108L, 117L, 112L, 120L, 105L, 119L, 110L, 117L, 117L, 115L, 116L, 
105L, 106L, 108L, 103L, 104L, 101L, 119L, 102L, 117L, 114L, 120L, 
106L, 120L, 105L, 103L, 107L, 102L), quantity = c(3L, 5L, 2L, 
3L, 3L, 2L, 2L, 1L, 3L, 5L, 2L, 3L, 4L, 4L, 2L, 2L, 4L, 2L, 1L, 
2L, 4L, 3L, 2L, 3L, 4L, 3L, 4L, 3L, 2L, 2L, 2L, 5L, 1L, 1L, 3L, 
4L, 3L, 1L, 3L, 1L, 2L, 3L, 2L, 4L, 2L, 2L, 5L, 2L, 1L, 5L, 1L, 
3L, 3L, 4L, 5L, 3L, 4L, 5L, 2L, 1L, 1L, 2L, 5L, 4L, 3L, 2L, 1L, 
4L, 1L, 4L, 4L, 5L, 2L, 2L, 4L, 2L, 5L, 4L, 5L, 4L, 1L, 2L, 5L, 
2L, 2L, 4L, 1L, 1L, 1L, 1L, 3L, 2L, 2L, 4L, 3L, 2L, 1L, 5L, 3L, 
4L, 2L, 5L), price = c(55L, 29L, 149L, 234L, 99L, 200L, 299L, 
67L, 35L, 199L, 27L, 200L, 234L, 299L, 499L, 67L, 27L, 499L, 
154L, 248L, 67L, 248L, 999L, 234L, 248L, 67L, 35L, 29L, 248L, 
248L, 72L, 248L, 72L, 72L, 154L, 55L, 149L, 149L, 154L, 999L, 
999L, 299L, 999L, 82L, 82L, 67L, 99L, 72L, 248L, 234L, 82L, 154L, 
234L, 82L, 29L, 234L, 82L, 29L, 35L, 199L, 199L, 299L, 234L, 
72L, 234L, 123L, 248L, 200L, 499L, 35L, 29L, 299L, 199L, 99L, 
120L, 999L, 200L, 99L, 234L, 29L, 299L, 999L, 999L, 72L, 499L, 
234L, 123L, 120L, 79L, 154L, 55L, 29L, 82L, 999L, 248L, 99L, 
123L, 99L, 234L, 79L, 27L, 82L)), class = "data.frame", row.names = c(NA, 
-102L))

The following code filters out all but user_id = 25 which is an edgecase as two different products were purchased on the first day.

30       25 2019-01-22        114        2   248
31       25 2019-01-22        115        2    72
32       25 2019-01-24        114        5   248
33       25 2019-01-27        115        1    72

test <- dat %>%
  arrange(user_id, created_at) %>% 
  group_by(user_id) %>%
  mutate(accepted_purchase = ((created_at != first(created_at)) & product_id != first(product_id))) %>% 
  filter(accepted_purchase)

length(unique(test$user_id))
[1] 24

I cannot seem to find the correct technique to filter that one out.

The expected result should be 23

There are several python and SQL solutions on the site, but I don't know enough of either to be able to translate them to R code.

dplyr r
1 Answers

In addition to your expression, you need to specify that the product_id is not duplicate AND both created_at and product_id are not equal to the first entry. So you'll just need to add !duplicated(product_id) to your code.

Also, since the result of your mutate is logical, you can directly use filter with the logical expression, without the need to set an extra column.

library(dplyr)

df %>% 
  arrange(user_id, created_at) %>% 
  group_by(user_id) %>%
  filter(created_at != first(created_at) & product_id != first(product_id) & !duplicated(product_id)) %>% 
  pull(user_id) %>% 
  unique() %>% 
  length()

[1] 23

# Here's the user_id for your reference
[1] 10 11 12 13 14 15 16 17 21 22 43 44 45 46 47 48 51 52 53 54 56 57 58
user_166474960
Related