UNIQUES count where ROLLING SUM > n

Lori

Hello

I know I can create a computation where I get users who performed action in the last N days and then filter the number of actions.

However, I want to get the number of users who performed the action N times in the rolling N days. 

Is it possible to achieve? I attached the SQL that is achieving what I want to achieve in Amplitude.

SELECT date, COUNT(DISTINCT user_id) FROM (<br />    SELECT<br />      user_id,<br />      date,<br />      SUM(score) OVER (<br />        PARTITION BY user_id<br />        ORDER BY date<br />        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW<br />      ) AS rolling_7_day_sum<br />    FROM table_name<br />)<br />WHERE rolling_7_day_sum > 25

The sql assumes that there is a row for every day for each user

JennRu

 Hi Lori could you set this up with an in-line segment in an event segmentation chart

who performed action x > 25 times

any time in each day

rolling over 7 days

 

And then set the event in the segmentation module, group on User ID, and apply formula PROPCOUNT(A1)

 

 

 

That’ll display a line chart of the unique count of eligible users who met the ROLLWIN + Count threshold criteria. 

Let me know if this helps!