Коэффициент удержания в когорте

Question

Я хочу провести некоторый когортный анализ на базе пользователей. У нас есть 2 таблицы "регистрации" и "сеансы", где у пользователей и сеансов есть поле "дата". Я пытаюсь сформулировать запрос, который выдает таблицу чисел (с некоторыми пробелами), которая показывает мне: количество пользователей, которые создали учетную запись в определенный день и у которых также есть созданный сеанс, указывая, что он вернулся в этот день 3-й день, 7-й день и 14 день.

создал_ в d1 d3 d7 d14
05/07/2007 12 * * *
04.07.2007 49 21 1 2
07.03.2007 45 30 * 3
02/07/2007 47 41 18 12

... В этом случае 47 пользователей, создавших учетную запись 2/07/2007, вернулись через 3 дня (d3)

Могу ли я выполнить это в одном запросе MySQL?

Answer 1

Да, ты можешь:

Select Signups.date as created at, 
count (distinct case when datediff(sessions.date, signups.date)=1 then signups.users else null end) as d1,
count (distinct case when datediff(sessions.date, signups.date)=3 then signups.users else null end) as d3,
count (distinct case when datediff(sessions.date, signups.date)=7 then signups.users else null end) as d7,
count (distinct case when datediff(sessions.date, signups.date)=14 then signups.users else null end) as d14 from signups
left join sessions using(users)
group by 1