How to limit user retention SQL query to interval after first user's first activity
I have written a SQL statement that gives user retention, day by day.
SELECT DATE(data_import.time), count(distinct data_import.user_id) AS active_users, count(distinct future_activity.user_id) AS retained_users, count(distinct future_activity.user_id) / count(distinct data_import.user_id)::float AS retention FROM data_import LEFT JOIN data_import AS future_activity ON data_import.user_id = future_activity.user_id AND DATE(data_import.time) = DATE(future_activity.time) - INTERVAL '1 day' GROUP BY 1
I believe that this is giving me user retention after any given day.
How can I limit this to give user retention only after the user's first activity? In other words, this user had activity the day after their first activity.
Let me know if this is unclear of if there are any questions.