Window function within group by in PostgreSQL

Question

I need a count for each user for every time the action field has been changed to the value 1. If the first entry is 1 that counts as well. The rows are out of order but should be counted in order by action_date.

In other words, what I believe needs to be done is: group the rows by user_id, order them by timestamp, and then count how often action=1 and action != the previous row.

Example

create table t (
user_id int,
action_date timestamp,
action int
);

Insert into t(user_id, action_date, action)
values
(1, '2017-01-01 00:00:00', 1),
(2, '2017-01-01 00:00:00', 0),
(1, '2017-01-03 00:00:00', 1),
(2, '2017-01-03 00:00:00', 0),
(1, '2017-01-02 00:00:00', 1),
(2, '2017-01-02 00:00:00', 1),
(1, '2017-01-04 00:00:00', 1),
(2, '2017-01-04 00:00:00', 1);

The result should be

 user_id | count 
---------+-------
       1 |     1
       2 |     2

With help from this answer I can get the results for a single account this way,

select user_id, count(*)
from (select user_id, action_date,action,lag(action) over(order by action_date) as prev_action
      from t where user_id=2
     ) t
where (action<>prev_action and action=1) or (action=1 and prev_action is null)
group by user_id;

but am stuck trying to expand it to all users.

Gurwinder Singh · Accepted Answer · 2017-03-20 20:27:46Z

3

Use the lag() function with partition by:

select user_id, count(*)
from (select t.*,
             lag(action) over (partition by user_id order by action_date) as prev_action
      from t
     ) t
where (action = 1) and (prev_action is distinct from 1)
group by user_id;

edited Mar 20, 2017 at 20:27

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

answered Mar 20, 2017 at 20:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rurp Over a year ago

I am getting the following error when I try this ERROR: syntax error at or near "(" LINE 3: lag(action) over (partition by user_id order by...

rurp Over a year ago

Thank you very much for that elegant answer, it works perfectly.

Collectives™ on Stack Overflow

Window function within group by in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related