I need to create another column in my dataset with number of previous specific events for each user (in my case number of completed levels)

My data looks like:

Desired result:

I tried to create column with 0/1 if event_name = 'complete_level', count cumulative sum per user and then join it to first table, but it return me an error Aggregate window functions with an ORDER BY clause require a frame clause.

select event_dttm, user_id, event_name, 
sum(completed_level) 
over (partition by event_dttm, user_id order by user_id, event_dttm) as completed_levels
from
(select event_dttm, user_id, event_name 
case
when event_name = 'complete_level' then 1
else 0
end as completed_level
from table)

Also I think that there is simpler solution for my task. Thank you!

CodePudding user response：

select
 *,
 sum(case when event_name = 'complete_level' then 1 else 0 end) over(partition by user_id order by event_dttm asc) as completed_cnt
from your_table