Assign ranks to records and keep the same rank based on condition-CodePudding

Suppose we have table below

user_id	event_name	event_time
Adam	subscribe	1
Adam	renewal	4
Adam	renewal	5
Adam	irrelevant event name	6
Adam	churn	7
Adam	subscribe	10
Adam	renewal	20

Notes: event_time is actually in milliseconds but i just simplified things.

I wanted to add numbers for each row so that final table looks like this:

user	event_name	event_time	subscription_id
Adam	subscribe	1	1
Adam	renewal	4	1
Adam	renewal	5	1
Adam	irrelevant event name	6	null
Adam	churn	7	1
Adam	subscribe	10	2
Adam	renewal	20	2
Adam	renewal	30	2
Adam	churn	40	2

What final table means is that first row is Adam's first subscription so subscription id is 1. Second row means Adam renewed his subscription of id 1 so renewal event is for subscription id 1. After n renewal events his first subscription ended(churned). So churn event row should have subscription id 1. After some time Adam resubscribes so this subscription should have id of 2 and every renewal events and churn event after this subscription should have id of 2 as well.

Basically whenever event_name is subscribe increment subscription_id by 1 and assign it for all relevant events, if event_name is irrelevant just assign null to subscription_id

Hope i explained my question well. Thanks for your effort and time.

CodePudding user response：

You might consider below query.

SELECT *,
       -- if event_name is irrelevant just assign null to subscription_id 
       IF(event_name IN ('subscribe', 'renewal', 'churn'),
          -- Everytime a user subscribes again, the user gets a new id increased by one.
          COUNTIF(event_name = 'subscribe') OVER (PARTITION BY user ORDER BY event_time),
          NULL
       ) AS subscription_id
  FROM sample_table;