CODEWITHSUNDEEP
sqlpostgresql
user2816085
user2816085

Reputation: 655

Count event if another event happened after it using SQL

I have a table with columns uid=user_id, pid=product_id, events and timestamp. I have to count total events per product. There are two ways to count events and then sum both results.

The event C will be counted directly per uid and pid.

Then I also have to count event A, only in case when event B is followed by event A.

UID    PID       EVENT       Timestamp
1      001        A          2007-11-09 T 11:20
1      001        B          2007-11-09 T 11:21
1      003        C          2007-11-09 T 11:45
2      009        A          2007-11-09 T 11:50
2      010        A          2007-11-09 T 11:51
3      011        A          2007-11-09 T 11:55
3      011        B          2007-11-09 T 11:56
4      004        A          2007-11-09 T 11:20
4      005        A          2007-11-09 T 11:20
3      006        C          2007-11-09 T 11:20
4      006        C          2007-11-09 T 11:20

The data shown above is manually created to best describe my scenario and it may not be perfectly fitting real world data.

Edit:- result should be like given below. Where I am trying to show number of events per product. 

pid     #event
001      1
003      1
004      0
005      0
006      2
009      0
010      0
011      1

Here is the fiddle: http://sqlfiddle.com/#!9/ccf421/4

Thank you for your help in advance.

Upvotes: 1

Views: 1100

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I think you want lag():

select uid,
       count(*) filter (where event = 'C') as cnt_c,
       count(*) filter (where event = 'B' and prev_event = 'A') as cnt_ab
from (select t.*,
             lag(event) over (partition by uid order by timestamp) as prev_event
      from t
     ) t
group by uid;

EDIT:

You say you want this "per product" and then to sum them. I think that is something like:

select pid,
       count(*) filter (where event = 'C' or event = 'B' and prev_event = 'A') as cnt
from (select t.*,
             lag(event) over (partition by pid, uid order by timestamp) as prev_event
      from t
     ) t
group by pid;

Upvotes: 2

Related Questions