Запрос на удержание — подсчет пользователей с условием BETWEEN в LEFT JOIN

Я создал запрос на удержание, который работает при попытке узнать, сколько уникальных пользователей из дня 0 вошли в систему в день 7 (или любую другую конкретную дату в будущем).

Однако проблема в том, что сейчас я пытаюсь увидеть, сколько пользователей из дня 0 вошли в систему с дня 1 до дня 7 (или любого другого будущего диапазона дат).

Вот текущий запрос, который выполняется, но не дает результатов:

select FirstTime, count(distinct(newusers)) as NewUsers, FutureDate
  , count(distinct(retaineduser)) as RetainedUser
from
(
  select nu.UserID as newusers, firsttime, l.UserID as retaineduser, l.LoginDate as FutureDate
  from ##newusers nu
  left join ##logins l on nu.UserID = l.UserID
  and FirstTime between dateadd(dd, -1, l.logindate) and dateadd(dd, -7, l.logindate)
  group by nu.UserID, FirstTime, l.UserID, l.logindate
) as x
group by FirstTime, futuredate
HAVING count(retaineduser) > 0

Кажется, я не могу понять, почему это не работает. Является ли использование условия BETWEEN плохой практикой в JOINS? Это на SQL сервере.

Образцы данных и желаемые результаты.

таблица новых пользователей:

NewUser - FirstTime
A - 2/1/2019
B - 2/1/2019
C - 2/1/2019
D - 2/2/2019

таблица логинов:

RetainedUser - LoginDate
A - 2/1/2019
A - 2/3/2019
A - 2/4/2019
B - 2/1/2019
B - 2/6/2019
C - 2/1/2019
C - 2/10/2019
D - 2/2/2019
D - 2/3/2019

Желаемый результат:

FirstTime - NewUsers - FutureDate - RetainedUser
2/1/2019 - 3 - 2/8/2019 - 2
2/2/2019 - 1 - 2/9/2019 - 1

Таким образом, желаемые результаты будут иметь первую дату и количество новых пользователей, которые вошли в систему тогда. Наряду с будущей датой это 7 дней в будущем с количеством пользователей, которые вошли в систему между 1 и 7 днями.

sql sql-server retention

John Doe 20.02.2019 источник

comment

@GordonLinoff Я отредактировал образцы данных и желаемые результаты в исходном сообщении. - John Doe 21.02.2019

Ответы (1)

arrow_upward
0
arrow_downward

select nu.FirstTime, 
    count(distinct nu.UserID) as newusers, 
    dateadd(dd, 7, l.FirstTime),
    count(distinct l.UserID) as retaineduser
  from ##newusers nu
  inner join ##logins l on l.UserID = nu.UserID
    and l.logindate between nu.FirstTime and dateadd(dd, 7, nu.FirstTime)
  group by nu.FirstTime, dateadd(dd, 7, nu.FirstTime)

Предикат присоединения с фиксированной датой. Вместо HAVING count(distinct l.UserID) as retaineduser > 0 вы можете просто переключиться с LEFT на INNER соединение (что означает "в списке одна совпадающая строка").

Ivan Starostin 21.02.2019