Skip to content
Advertisement

Based on date range how to get unique, new, and repeat results?

I have a database table with datetime field and also contains user id (foreign key). I need to get the activity of unique, new, and repeat users.

If a user does some activity, a database row is inserted in this table. So, within a date range (from date-to date), I am mentioning those users as unique who has done activities one or more times. Like count of distinct users is the number of unique users.

Then I need to get the number of those users who have activities within the date range and not out of that date range as new users and who have activities within the date range and also out of that date range as repeat users

Advertisement

Answer

Unique users

SELECT DISTINCT UserId FROM Table
WHERE Date BETWEEN StartDate AND EndDate

New users

SELECT DISTINCT UserId FROM Table
WHERE Date BETWEEN StartDate AND EndDate
AND UserId NOT IN (SELECT UserId FROM Table
                   WHERE Date NOT BETWEEN StartDate AND EndDate)

Repeat users

SELECT DISTINCT UserId FROM Table
WHERE Date BETWEEN StartDate AND EndDate
AND UserId IN (SELECT UserId FROM Table
               WHERE Date NOT BETWEEN StartDate AND EndDate)
7 People found this is helpful
Advertisement