Skip to main content

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 (
    SELECT
      user_id,
      date,
      SUM(score) OVER (
        PARTITION BY user_id
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
      ) AS rolling_7_day_sum
    FROM table_name
)
WHERE rolling_7_day_sum > 25

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

Hi @Lori 

I’m not sure if I’m oversimplifying your use case here, but you can build this clause in a cohort definition or a user segment as seen below.

This rolling cohort definition will give you users who have performed the action N times over the last N days.

Let me know if I have interpreted your use case correctly.

Yes, unfortunately this is a oversimplified solution. 

What I’d like is to see a line chart, where if I look at June 5th I get the UNIQUES of users who performed action N times between May 30th and June 5th. If I look on June 1st, I get the UNIQUES of users who performed action N times between May 26th and June 1st. All in one chart. 

Basically the rolling SUM of the 7 day window from the one that I am investigating on a line chart.

 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!

Thanks for jumping in @JennRu !

Yes, That does solve the problem, thank you @JennRu 

 

Reply


Terms & ConditionsCookie settings