Search code examples
sqlsql-serverretention

Retention Query - Counting Users with BETWEEN Condition in LEFT JOIN


I've created a retention query that works when trying to find out how many unique users from Day 0 logged in on Day 7 (or any other specific future date).

However, the problem is that now I'm trying to see how many users from Day 0 have logged in from Day 1 to Day 7 (or any other future date range).

Here's the current query that runs, but produces no results:

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

I can't seem to wrap my head around why this isn't working. Is using a BETWEEN condition considered bad practice on JOINS? This is on SQL Server.

Sample data and desired results.

newusers table:

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

logins table:

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

Desired Result:

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

So the desired results would have the first date and amount of new users that logged in then. Along with the future date that's 7 days in the future with the amount of users that logged in between day 1 and day 7.


Solution

  • 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)
    

    Fixed date join predicate. Instead of HAVING count(distinct l.UserID) as retaineduser > 0 you can just switch from LEFT to INNER join (which means "at list one matching row").