Count based on certain timestamp filter criteria

[chaorenzhu]

Hi experts I have a question.

See my sample data below:

load * inline [
customer_id, activity, activity_date

A, search, 2021-04-06

A, search, 2021-04-07

A, purchase, 2021-04-08

A, purchase, 2021-04-09

A, search, 2021-04-10

B, purchase, 2021-04-10

C, purchase, 2021-04-11

C, search, 2021-04-12];

I want to count how many customer_id have activity of "purchase" after "search". In above example only A fulfills the requirement, as B has no "search" and C has "purchase" before "search".

Thank you for the kindly help.

[justISO · Accepted solution]

Hi, as 'purchase' and 'search' has no link we can decide to which 'purchase' which 'search' belong, we need to create full list of 'possible combinations' of dates for each customer_id. This can be done for each customer taking purchase date and then adding search date and then looking which date comes after witch. Finally we count distinct customer_id with positive indicator, as you can see one customer can have 2 possible values:

sample:
load * inline [
customer_id, activity, activity_date
A, search, 2021-04-06
A, search, 2021-04-07
A, purchase, 2021-04-08
A, purchase, 2021-04-09
A, search, 2021-04-10
B, purchase, 2021-04-10
C, purchase, 2021-04-11
C, search, 2021-04-12];

NoConcatenate
temp:
Load 
customer_id,
activity_date as search_date
Resident sample
where activity='search';

join 
Load 
customer_id,
activity_date as purchase_date
Resident sample
where activity='purchase';

drop table sample;

main:
Load *, if(search_date<=purchase_date, 1,0) as ind
Resident temp;

drop table temp;

aggr:
load
count(DISTINCT customer_id) as count
resident main
where ind=1;

[chaorenzhu]

Thanks @justISO  brilliant!