Get last value within each group based on date

[jakobjensen]

I have the following table

user_id   level   date
1          0      2021-01-01
1          1      2021-01-15
1          0      2021-02-01
2          1      2021-01-01
2          2      2021-02-17
3          2      2021-03-15
3          0      2021-04-13
4          1      2021-01-01

note the dates correspond to a change in the level i.e the last "level" recorded is their current level. I want to calculate how many there's in each level (theres level 0,1,2 and 3) i.e I need for each "user_id" get the last "level" value recorded which in the example above would be

level   count
0        2     #user_id=1,3
1        1     #user_id=4
2        1     #user_id=2

[rubenmarin]

Hi, for the count you can use:

Sum(Aggr(If(date=max(TOTAL <user_id> date), 1,0),level,date,user_id))

For the text with users id's:

='#user_id=' & Concat(DISTINCT Aggr(If(date=max(TOTAL <user_id> date), user_id),level,date,user_id),',')