Add derived column from existing column based on a condition in bigquery

Question

Suppose we have table below

user_id	event_name	event_time
Adam	subscribe	1
Adam	renewal	4
Adam	renewal	5
Adam	churn	7
Adam	subscribe	10
Adam	renewal	20

Notes:

• event_time is actually in milliseconds but i just simplified things.
• I asked a similar question and got a pretty good answer in here: Assign ranks to records and keep the same rank based on condition, may be useful for those looking for an answer to this question.

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

user	event_name	event_time	subscription_time
Adam	subscribe	1	1
Adam	renewal	4	1
Adam	renewal	5	1
Adam	churn	7	1
Adam	subscribe	10	10
Adam	renewal	20	10
Adam	renewal	30	10
Adam	churn	40	10

To explain, each renewal event belongs to preceding subscribe event. I need a derived column that shows the time for that subscription event. Therefore derived column should be same as a subscription time of that event. My final purpose is to find out number of renewals/churns etc for a given subscription time

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

Answer 1

Consider below approach similar to previous my answer.

SELECT *,
       IF(event_name IN ('subscribe', 'renewal', 'churn'),
          -- below will return most recent time of *subscribe* event
          LAST_VALUE(IF(event_name = 'subscribe', event_time, NULL) IGNORE NULLS) OVER (PARTITION BY user ORDER BY event_time),
          NULL
       ) AS subscription_time
  FROM sample_table;

Query results