'MySQL query calculate user retention in a certain date-range

I'm trying to select the count of users retaining in a certain date range and that was successful using this query and the table below:

+----------+-------------+
| Field    | Type        |
+----------+-------------+
| id       | varchar(17) |
| log_date | date        |
+----------+-------------+

SELECT last_day.log_date, COUNT(distinct last_day.id) as users_num
FROM (SELECT DISTINCT log_date, id
      FROM `userActivity`) this_day
JOIN (SELECT DISTINCT log_date, id
      FROM `userActivity`) last_day
ON this_day.id = last_day.id
AND this_day.log_date = "2018-10-01"
AND last_day.log_date BETWEEN "2018-10-01" AND "2018-10-30"
GROUP BY log_date;

But the problem that I'm facing is that I want the assume that every day of the date-range is day 0. (similar to the following example):

Note that the first row in the pic is the avg of the below results I need to calculate. Anyone has any idea how can I enhance my query to get the result like the picture?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'MySQL query calculate user retention in a certain date-range

Sources

Related Questions