I have a transaction data like this:

I want to calculate a monthly rolling unique count of users which updates on a weekly basis. The week must be a full week that starts from Monday to Sunday.

Here is the desired output:

and so on.. data goes back 3 years

Using the code below, I am able to get the first row of the desired output but not sure how to get row 2 and 3 (and going back 3 years).

SELECT count(distinct user_id) as Unique_User_ID_Count, min(Purchase_Date) as start_week_date, max(Purchase_Date) as end_week_date
    FROM table
    WHERE Purchase_Date>= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), WEEK(MONDAY)) AND Purchase_Date<= DATE_TRUNC(CURRENT_DATE()-6, WEEK(SUNDAY))

Any help is appreciated

CodePudding user response：

You could use CTEs to compute the auxiliary data you need. With your starting dataset, I would do the following:

with data as (
   select
      User_ID,
      Purchase_Date,
      DATE_TRUNC(Purchase_Date, WEEK(MONDAY)) as start_week_date,
      DATE_ADD(DATE_TRUNC(Purchase_Date, WEEK(MONDAY)), INTERVAL 6 DAY) as end_week_date,
   from your_database
)

select distinct
   count(distinct User_ID) over (partition by first_day_week, last_day_week) as Unique_User_ID_Count,
   first_day_week,
   last_day_week,
from data

That should work.

CodePudding user response：

i think what you need is something like this..

select 
DATEADD(DAY, 1-DATEPART(WEEKDAY, DateField) 1, convert(int,DateField)),DATEADD(DAY, 1-DATEPART(WEEKDAY, DateField) 7, convert(int,DateField)),count(*)
from Table1

group by DATEADD(DAY, 1-DATEPART(WEEKDAY, DateField) 1, convert(int,DateField)),DATEADD(DAY, 1-DATEPART(WEEKDAY, DateField) 7, convert(int,DateField))

if the data is big.. i d convert date to float then div to 7 then convert to int.. which i think can group the same results.. but then you are gonna have some more trouble in frontend..