название было бы слишком многословным, если бы я действительно попытался втиснуть все это туда, но вот с чем мне нужна помощь ...
Мы пытаемся подсчитать удержание пользователей. У наших пользователей даты начала и окончания назначения могут совпадать. Что мне нужно сделать, так это просмотреть все назначения кандидатов и определить, сохраняются ли они (30 дней или меньше между предыдущим завершением и новым началом). Сложная часть: мне нужно присвоить удержание предыдущей дате окончания назначения. Вот предварительный просмотр данных:
month | user_id | start_date | end_date | rank | days_btw_assignment
1 5 1-1-16 1-31-16 1 NULL
2 5 2-14-16 4-15-16 2 15
6 4 6-01-16 11-01-16 1 NULL
8 4 8-01-16 11-01-16 2 -81
Поэтому для пользователя 5 мне нужно было бы присвоить удержание месяцу 16 января, потому что дата окончания их назначения заканчивается 1–31–16. Для пользователя 4, где назначения перекрываются, я бы отдал кредит на удержание 16 ноября, потому что их предыдущая дата окончания назначения заканчивается 11.01.16.
Я ограничил этот пример случаями использования, когда у них есть только 2 назначения, хотя их может быть и больше. Мне просто нужен шаг в правильном направлении, и я, вероятно, смогу справиться со всеми другими вариантами использования самостоятельно.
Вот пример кода, который я использую сейчас:
with placement_facts as (select date_trunc('month',assignment_start_date) as month, user_id, assignment_start_date, assignment_end_date, rank () over (partition by user_id order by assignment_start_date asc), extract( day from assignment_start_date - lag(assignment_end_date, 1) over (partition by user_id order by assignment_start_date asc)) as time_btw_placement
from activations as ca
join offers on ca.offer_id = offers.id
where assignment_start_date != assignment_end_date
order by 2,4 asc)
select placement_facts.month, count(distinct case when time_btw_placement <=30 then user_id else null end) as retained_raw
from placement_facts
group by 1;
Цените помощь и, пожалуйста, lmk, если мне нужно что-то уточнить!
