DATE_ADD(act1.event_date, INTERVAL 1 DAY) = act2.event_date
we can use function to add 1 more day into event_date.
Below is the solution of this question:
SELECT ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) as fraction
FROM Activity
WHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
IN (SELECT player_id, MIN(event_date) AS first_login FROM ACTIVITY GROUP BY player_id)
DATE_SUB(NOW(),INTERVAL 24 HOUR) only need to be calculated once,
while DATE_ADD(datetimecolumn,INTERVAL 24 HOUR) need to do with every records
DATE_SUB('2024-04-07', INTERVAL 1 DAY) will subtract 1 day from the date '2024-04-07', resulting in '2024-04-06'.
DATE_ADD('2024-04-07', INTERVAL 1 DAY) will add 1 day to the date '2024-04-07', resulting in '2024-04-08'.
Question
This question is for date function manipulation,
Below is the solution of this question: