Distinct count of users in the last 7 days for the last 7 days-CodePudding

I want to count the number of unique users in the last 7 days for the last 7 days. I want to build a table that has a column of dates (the current day to 7 days into the past) and the corresponding value is the number of unique users 7 days into the past. I have a calendar table to use that has daily dates.

Example of available data:

insert_date  |  user
1-1-22       |  A
1-1-22       |  B
1-1-22       |  C
1-3-22       |  D
1-3-22       |  A
1-5-22       |  B
1-9-22       |  C
1-11-22      |  D
1-11-22      |  F

Desired output:

date         | distinct users
1-5-22       | 4
1-6-22       | 4
1-7-22       | 4
1-8-22       | 3
1-9-22       | 4
1-10-22      | 2      (e.g. 1-4-22 to 1-10-22 -> B, C)
1-11-22      | 4      (e.g. 1-5-22 to 1-11-22 -> B, C, D, F)

CodePudding user response：

In this query we create a list of days using DISTINCT and then count the rows where the date is in the 7 previous days.

CREATE TABLE create_user(
  insert_date date,
  user_name varchar(10)
  );

insert into create_user values
('2022-1-1','A'),
('2022-1-1','B'),
('2022-1-1','B'),
('2022-1-3','D'),
('2022-1-3','A'),
('2022-1-5','B'),
('2022-1-9','C'),
('2022-1-11','D'),
('2022-1-11','F');

SELECT 
  d.insert_date,
  count(cu.user_name) count_7d
FROM (SELECT DISTINCT insert_date FROM create_user) d
JOIN create_user cu
ON d.insert_date >= cu.insert_date
WHERE DATEDIFF(second,d.insert_date, cu.insert_date) <= 7
GROUP BY d.insert_date
ORDER BY d.insert_date;

insert_date | count_7d
:---------- | -------:
2022-01-01  |        3
2022-01-03  |        5
2022-01-05  |        6
2022-01-09  |        7
2022-01-11  |        9

db<>fiddle here

CodePudding user response：

Given you have a Calendar Table, something like the following should suffice:

With a Calendar table and column named CalendarDate, use an apply to count users per date:

select d.calendardate, u.qty
from CalendarTable d
outer apply (
    select Count(distinct user_name)
    from create_user u
    where u.insert_date = d.CalendarDate
)u(qty)
where d.CalendarDate >= DateAdd(day,-7,GetDate()) and d.CalendarDate <= GetDate();