1

I Need to get the weekly retention users. If user has made the transaction in week 4, that user is there in Week 0, week 1, week 2, week 3, week 4.

If User made transaction in week 0, and week 3 then user will be there in Week 0, week 1, week 2, week 3 Thanks @bbaird,

I am updating the question with precise information and question

Here is my table data

TxnId userid transactedWeek
T1 U1 0
T2 U2 0
T3 U3 0
T4 U4 0
T5 U1 0
T6 U2 0
T7 U6 0
T8 U1 1
T9 U8 1
T10 U12 1
T11 U3 1
T12 U5 1
T13 U9 2
T14 U15 2
T15 U20 3
T16 U15 3
T17 U15 3
T18 U8 3
T19 U9 4
T20 U20 5

The Result I am looking for

Week active_users_count
0 11
1 8
2 4
3 4
4 2
5 1

Users count is kinda cumulative but unique users.

For example if user-1 has performed transaction 3 times in week-0, then count for week-0 is 1

If User-1 has performed 3 transactions in week-0, 2 transaction in week-2, 1 transaction in week-3, then count will be like this

week-0 -> 1 (i.e. he has performed transaction >= week 0)

week-1 -> 1 (i.e. he has performed transaction >= week 1)

week-2 -> 1 (i.e. he has performed transaction >= week 2)

week-3 -> 1 (i.e. he has performed transaction >= week 3)

There can be any number of weeks. It is not fixed to 4 weeks or 8 weeks.

Mallikarjun
  • 13
  • 4

1 Answers1

0

This should be a relatively straightforward aggregation by Week, but you will need to join all records greater than or equal to the current week and take your distinct count from there.

However, that's going to be a lot of rows for Bigquery to work through, so this will probably work better for the platform:

SELECT
  TransactedWeek AS Week
 ,SUM(SUM(LastWeekInd))
    OVER
      (
        PARTITION BY 
          1 
        ORDER BY 
          TransactedWeek
        ROWS BETWEEN 
          CURRENT ROW
            AND
          UNBOUNDED FOLLOWING
      ) AS active_users_count
FROM
  (
    SELECT DISTINCT
      UserId
     ,TransactedWeek
     ,CASE
        WHEN TransactedWeek = MAX(TransactedWeek) OVER (PARTITION BY UserId) THEN 1
        ELSE 0
      END AS LastWeekInd
    FROM
      <Your Table>
  ) x
GROUP BY
  TransactedWeek

You can add logic to limit the ranges in the inner query.

Note: if you have any weeks missing from your data, they will be missing in your final result (although the counts will still be accurate). If you have weeks with no transactions, you may need to generate a list of weeks and left join these results to that.