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.

7
  • Hi, welcome to dba.SE. Please post table definitions (relevant columns only) and sample data so others can better assist you.
    – bbaird
    Jan 7 '21 at 14:32
  • Hi, the sample data is provided in the link that i have posted. As the data i have added is 1000s of rows, i provided as link. Jan 7 '21 at 14:34
  • Links are often blocked by employers (and may not be durable) so it's better to include the sample data AND table definitions in your question.
    – bbaird
    Jan 7 '21 at 14:40
  • Thanks for making me aware of blocking the links. I have updated with sample data in body. but as the limited num of chars, i have given some limited data. Jan 7 '21 at 15:00
  • You can just provide enough information so a reasonable person could deduce what you have and what you're trying to do.
    – bbaird
    Jan 7 '21 at 15:06
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.

4
  • Hi, thanks for responding but the results is not correct. As your query groups and counts for that particular week. But check the sample data and result. count is not at that particular week but subsequent weeks as well Jan 7 '21 at 17:29
  • According to your query, week-0 have count of 5, but it actually has to be 11 Jan 7 '21 at 17:30
  • @Mallikarjun fixed
    – bbaird
    Jan 7 '21 at 17:49
  • Worked Like a charm. Thanks for saving the day. :) Jan 7 '21 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.