Hola, posteo para ver si me podeis echar una mano con un problema que tengo para hacer un cohort analysis, ya que llevo un rato con ello y no me sale.
Tengo una base de datos de usuarios, donde tengo 2 tablas "users" y "sessions". La tabla "users" tiene el campo "created_at" y la tabla "sessions" tiene el campo "start_at". Lo que quiero calcular, es que me diga cuantos usuarios que se han registrado en una semana en concreto, y cuantos de los registrados han vuelto a conectarse en los siguientes 7,14,21 días
Un ejemplo de lo que digo.
Semana 14
Usuarios registrados: 100
Cuantos de esos 100 se han conectado en los siguientes 7 días? : 97
Cuantos de esos 100 se han conectado en los siguientes 14 días? : 56
Cuantos de esos 100 se han conectado en los siguientes 21 días? : 32
La formula que estoy empleando es la siguiente:
select
week(from_unixtime(up.registered_at)) week,count(distinct up.id) registrados,
coalesce(count(distinct(CASE WHEN datediff(from_unixtime(up.registered_at),from_unixtime(us.start_at)) >=0 AND datediff(from_unixtime(up.registered_at),from_unixtime(us.start_at)) <= 7 then up.id end))) as 0to7,
coalesce(count(distinct(CASE WHEN datediff(from_unixtime(up.registered_at),from_unixtime(us.start_at)) >=8 AND datediff(from_unixtime(up.registered_at),from_unixtime(us.start_at)) <= 14 then up.id end))) as 8to14,
coalesce(count(distinct(CASE WHEN datediff(from_unixtime(up.registered_at),from_unixtime(us.start_at)) >=15 AND datediff(from_unixtime(up.registered_at),from_unixtime(us.start_at)) <= 21 then up.id end))) as 15to21
from ftmanager_userprofile up
left join ftmanager_usersession us
on up.id=us.user_id
group by 1
limit 100
El problema viene que solo me calcula los registrados de dicha semana y cuantos se conectaron dentro de esos 7 días.. pero el resto de semanas me sale en todos los campos "0".
Alguna ayuda please?
Gracias
