How to write query for this problem ?

[Kumar Vivek]

So, I have a table which contains following columns - event_id, domain_userid, collector_tstamp, se_action.

Now se_action can be following - SERP view through category, SERP view through location, AdDetails, SomeConversion.

I want events/users where SERP view through category -> AdDetails -> SomeConversion happened.

There is a possibility that user can go to SERP category then SERP location and then view ad but that should not be counted. It must be SERP category then ad view and then some conversion event.

Possibility 1: consider

SERP category

Ad deatils

conversion

Possibility 2: don't consider

SERP category

SERP location

Ad details . .

Possibilty 3: consider and count should be 2 for SERP -> Ad detail

SERP category

Ad details

Back to SERP category

Another Ad

Possilbilty 4: count 2 for SERP -> Ad detail and count 1 for SERP -> Ad detail -> conversion

SERP category

Ad details

Conversion

Back to SERP category

Another ad

I am unable to find out how to write query for this.