还是计算老用户和活跃用户的问题

IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 'test')
    DROP TABLE test
GO
CREATE TABLE test(id INT IDENTITY , username VARCHAR(02) , time1 DATE )
GO
INSERT INTO test 
SELECT 'aa' , '2014.1.1' UNION ALL
SELECT 'bb' , '2014.1.1' UNION ALL
SELECT 'cc' , '2014.1.1' UNION ALL
SELECT 'aa' , '2014.1.2' UNION ALL
SELECT 'bb' , '2014.1.2' UNION ALL
SELECT 'aa' , '2014.1.3' UNION ALL
SELECT 'dd' , '2014.1.3' UNION ALL
SELECT 'aa' , '2014.1.4' UNION ALL
SELECT 'bb' , '2014.1.4' 
 
 
--print dateadd(dayofyear,-7, getdate() )
SELECT a.TIME1,
       (
           SELECT COUNT(1)
           FROM   (
                      SELECT username
                      FROM   test AS b
                      WHERE  b.time1 = a.time1
                             AND username IN (SELECT d.username
                                              FROM   test AS d
                                              WHERE  d.time1 < a.time1)
                      GROUP BY
                             b.username
                  ) AS d
       ) AS 老用户
FROM   test AS a
--WHERE time1 = CONVERT(DATE , GETDATE()) --如果要取当天的加上这个条件
GROUP BY
       a.time1
        
------------结果
TIME1      老用户
---------- -----------
2014-01-01 0
2014-01-02 2
2014-01-03 1
2014-01-04 2

(4 row(s) affected)