Nested Loops and incremental counter

[GauravG]

Hello,

I have a table "Daily_User_Level_Details" as follows :

Date	User	Attempts
2023-01-23	AB	2
2023-01-23	CD	1
2023-01-23	EF	3
2023-01-24	AB	1
2023-01-24	EF	1
2023-01-24	MN	2

Everyday I want to count the number of users and their total attempts "till date" - per day in buckets of "1 attempt", "2 attempts", "3 or more attempts". Hence, for the above scenario the resultant table "Attempt_Aggregation" would be like (only consider date, bucket, UserCount column. the 4th column reason is just for explanation in this post) :

Date	Bucket	UserCount	Reason
2023-01-23	1 attempt	1	Only 1 user i.e. CD has 1 attempt on 1/23
2023-01-23	2 attempts	1	Only 1 user i.e. AB has 2 attempts on 1/23
2023-01-23	3 or more attempts	1	Only 1 user i.e. EF has 3 attempts on 1/23
2023-01-24	1 attempt	0	AB and EF are not new users. They had 2 and 3 attempts respectively yesterday
2023-01-24	2 attempts	1	MN has 2 attempts on 1/24 and 0 for all dates before 1/24 because MN new user
2023-01-24	3 or more attempts	2	AB's 2 attempts on 1/23 + 1 attempt on 1/24 = 3 in total "till date" and EF's 3+1 = 4 attempts till date. Therefore there are 2 users who fall in "3 or more" bucket i.e. AB and EF

I can do this using aggr() function at the chart level. However, we have a date range of 2 years and more than 40,000 users per day. This will make it very slow. Hence I want to do this at script level and store each day's calculation in a QVD on an incremental level, so that we dont have to compute the historical numbers every day. But I am struggling with developing the logic this way. Hence need some guideline / direction. Please help.

Thank you.

[MayilVahanan · Accepted solution]

Hi

Try like below

Temp:
LOAD * INLINE [
Date,User,Attempts
2023-01-23,AB,2
2023-01-23,CD,1
2023-01-23,EF,3
2023-01-24,AB,1
2023-01-24,EF,1
2023-01-24,MN,2
];

Load *, if(TotalAttempts >=3, '3+', TotalAttempts) as AttemptBucket;
Load *, if(User = Previous(User), 1, 0) as OldUser, if(User = Previous(User), Peek(Attempts)+Attempts, Attempts) as TotalAttempts Resident Temp Order by User, Date;

DROP Table Temp;

Front End:

Dim: Date, AttemptBucket

Exp: Count(User) or Count(Distinct User)

Depends on ur requirement.