I want to get daily user retention. (Hive) I have the user's login history. Based on the search date, I would like to find the number of users who accessed from D+0 to D+4 days. (If you haven't logged in even one day, it considered to have left.)
This is my table -
| user_no | log_date | | 100101 | 2023-5-10 | | 100102 | 2023-5-10 | | 100103 | 2023-5-10 | | 100101 | 2023-5-11 | | 100102 | 2023-5-11 | | 100104 | 2023-5-11 | | 100101 | 2023-5-12 | | 100103 | 2023-5-12 | | 100104 | 2023-5-12 | | 100106 | 2023-5-13 | | 100103 | 2023-5-13 |
....
want to get -
| log_date | re_user | | 2023-5-10 | 3 | | 2023-5-11 | 2 | | 2023-5-12 | 1 | | 2023-5-13 | 0 | | 2023-5-14 | 0 |
