Conditional row counter in postgres

Question

I have the following data in my postgres table:

user id     time_stamp                action

1      '2014-12-04 17:44:46+00'     sign up
1      '2015-03-26 15:06:27+00'      work out
3      '2015-02-09 11:40:25+00'      sign up

what i want is to create a 4th column

that counts the actions done by the user based on time stamp which would look like this:

user id   time_stamp             action    event id

1    '2014-12-04 17:44:46+00'    sign up    1-1 ( 1st action of user id 1
1    '2015-03-26 15:06:27+00'    work out   1-2 (2nd action of user id 1
3    '2015-02-09 11:40:25+00'    sign up    2-1 ( 1st actoin of user id 1

this problem has been bothering me for a few months now.. would really really appreciate if anyone can help me solve it or guide me in the right direction..

klin · Accepted Answer · 2015-07-22 08:42:48Z

1

You should userow_number() function. Assuming that your table name is users:

alter table users add column event_id text;

update users t
set event_id = e_id
from (
    select *, 
        format('%s-%s', user_id, row_number() over (partition by user_id order by time_stamp)) as e_id
    from users
    order by 1, 2
    ) sub
where sub.user_id = t.user_id and sub.time_stamp = t.time_stamp;

answered Jul 22, 2015 at 8:42

klin

123k15 gold badges241 silver badges263 bronze badges

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

1 Comment

thealchemist Over a year ago

klin... you are a frikkin genius... how the hell did you do that??? amazing man!!! i cannot thank you enough... :)) it works!!

Collectives™ on Stack Overflow

Conditional row counter in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related