x

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
more ▼

asked Sep 25, 2012 at 12:47 PM in Default

avatar image

Chacika
0 1 1 1

You are going to have to show what the raw data looks like, before we can even attempt to answer.

Sep 25, 2012 at 12:55 PM Kev Riley ♦♦

Well ! the raw data are columns DateId and UserId. DistinctUsersForLast7Days is the column which i need to update in the table.

Sep 25, 2012 at 01:01 PM Chacika

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/

Sep 25, 2012 at 01:05 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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;
more ▼

answered Sep 25, 2012 at 03:59 PM

avatar image

MarkX
60 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2221
x53
x12

asked: Sep 25, 2012 at 12:47 PM

Seen: 926 times

Last Updated: Sep 25, 2012 at 03:59 PM

Copyright 2018 Redgate Software. Privacy Policy