Hi everyone,
I have a general question about what the best approach is to solve a problem.
I have two tables:
Usage table
| UserID | Month | Page loads |
| 111 | April 2018 | 12 |
| 111 | June 2018 | 3 |
| 222 | January 2018 | 6 |
| 333 | March 2018 | 2 |
Users table
| UserID | Name | Subscription start | Subscription end |
| 111 | Jon | June 2017 | July 2019 |
| 222 | Jackie | December 2017 | March 2018 |
| 333 | Tom | February 2018 | July 2018 |
| 444 | Millie | January 2018 | September 2018 |
I need to report on users with low usage and look at trends month by month. The definition of a user with low usage is 5 page loads or less over a rolling 3 month period (ie the April figure would show users who had 0 to 5 page loads during the Feb-Apr period).
Two additional factors are:
- The Usage table only includes users who did visit the website. So it doesn't include the users with 0 visits.
- For each monthly total of inactive users it only makes sense to include the users who's subscription was valid during that month.
So I need to check whether the month falls inside the user's subscription start and end dates, and then count the number of page loads they had during that month and the two months previous. And then count the number of users where that page load total is 5 or less for that month.
The final output would be a bar chart with Months along the axis and the values counting the number of inactive users during each of those months.
I'm not sure about how to approach this challenge, or where to start with breaking it down in smaller steps... any advice very welcome as always, thank you!
