asked
count users for past 7 days
Hi, I am trying to write a query to count distinct UserIds for a rolling prior 7 days period. Result set for DistinctUsersForLast7Days should be like: DateId UserId DistinctUsersForLast7Days 20120903 2 1 20120904 1 2 20120905 2 2 20120906 3 3 20120907 1 3 20120908 2 3 20120909 3 3 20120910 2 3 20120911 1 3 20120912 2 3 20120913 1 3 20120914 2 3 20120915 2 3 20120916 1 2 20120917 1 2
You are going to have to show what the raw data looks like, before we can even attempt to answer.
So the data you posted is both the raw and the expected result? I see ! You want to look at examples such as Jeff Moden's Running Total solution :
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Try this WITH Data(DateId,UserId) AS ( SELECT CAST('20120903' AS DATE), 2 UNION ALL SELECT CAST('20120904' AS DATE), 1 UNION ALL SELECT CAST('20120905' AS DATE), 2 UNION ALL SELECT CAST('20120906' AS DATE), 3 UNION ALL SELECT CAST('20120907' AS DATE), 1 UNION ALL SELECT CAST('20120908' AS DATE), 2 UNION ALL SELECT CAST('20120909' AS DATE), 3 UNION ALL SELECT CAST('20120910' AS DATE), 2 UNION ALL SELECT CAST('20120911' AS DATE), 1 UNION ALL SELECT CAST('20120912' AS DATE), 2 UNION ALL SELECT CAST('20120913' AS DATE), 1 UNION ALL SELECT CAST('20120914' AS DATE), 2 UNION ALL SELECT CAST('20120915' AS DATE), 2 UNION ALL SELECT CAST('20120916' AS DATE), 1 UNION ALL SELECT CAST('20120917' AS DATE), 1) SELECT t1.DateId,t1.UserId, COUNT(DISTINCT t2.UserId) AS DistinctUsersForLast7Days FROM Data t1 INNER JOIN Data t2 ON t2.DateId BETWEEN DATEADD(Day,-7,t1.DateId) AND t1.DateId GROUP BY t1.DateId,t1.UserId ORDER BY t1.DateId;
9 People are following this question.
