Percent of months logged in
Question
You're given the following table showing user logins to your system:
Table: login_info
user_id | date |
---|---|
13412 | 2020-01-05 |
13412 | 2020-04-25 |
13412 | 2020-08-31 |
13412 | 2020-09-02 |
... | ... |
Using the table above, write a SQL query that returns percent of months each user has logged in to your system. Your numerator will be the number of months a user has logged in, and your denominator will be the total number of months that a user has been registered in your system (here you can use the user's last login - first login as a proxy for this metric).
Here's an example of what your output table should look like:
user_id | pct_months |
---|---|
13412 | X% |
23809389 | Y% |
You can view/query the data in an interactive SQL fiddle here.