Hey folks, I have data that looks like this:
Date ID Status
2019.01.15 USER1 NEW
2019.01.30 USER1 REGISTERED
2018.11.17 USER2 NEW
2019.01.18 USER2 REGISTERED
2019.01.19 USER3 NEW
2019.01.26 USER3 REGISTERED
2019.01.30 USER3 ACTIVE
2019.03.22 USER3 SUSPENDED
2019.03.24 USER3 ACTIVE
2019.01.24 USER1 ACTIVE
2019.01.25 USER4 NEW
2019.02.26 USER1 SUSPENDED
2019.04.21 USER3 SUSPENDED
What I need is a daily account of all "last statuses". Meaning that I need to be able to tell that on any given day, how many ACTIVE, NEW, REGISTERED, SUSPENDED statuses were there (On any specific date, what was the last status of the users relative to that day, and then do a count).
I wonder if I need to write in all IDs to a table for all the days in the range (2 years and growing) or this can be done dynamically / easier. Later down the road, I will need monthly, quarterly summaries as well on the average values of the status counts.
Any simple way to do this? Much appreciated in advance!
