question

Chacika avatar image
Chacika 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
sql-server-2008countdate-range
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You are going to have to show what the raw data looks like, before we can even attempt to answer.
0 Likes 0 ·
Well ! the raw data are columns DateId and UserId. DistinctUsersForLast7Days is the column which i need to update in the table.
0 Likes 0 ·
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/
0 Likes 0 ·

1 Answer

· Write an Answer
MarkX avatar image
MarkX answered
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;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.