Counting Distinct users over time

Question

Data is thus:

Date       | userid 
-------------------
01/01/2016 | jacby
01/01/2016 | jacby
01/01/2016 | donta
01/01/2016 | stapf
02/02/2016 | kamed
02/02/2016 | jacby
02/02/2016 | kamed
02/02/2016 | carpi
03/03/2016 | slwig
03/03/2016 | kamed

What I would like to be able to do is produce an output, using SQL, TSQL or SSRS expressions, that looks like:

Date       | Unique Users
-----------------------------
01/01/2016 | 3
02/02/2016 | 5
03/03/2016 | 6

which is essentially a running total of unique users, in reference to unique users up to that date. I.e. from a start day, i want to know the total number of unique users since the start date, ongoing.

I can do a running total of unique users on a particular day, but that doesn't account for if that user had logged in on a previous day. I did wonder about a while loop using the date as a counter, but really, I don't know where to start with that.

My google-fu is way off and I can't think of the correct terminology to describe what I am looking for.

EDIT - Apologies - there may be more than one entry per day for each user. I have amended the source example table above.

Answer 1

You want to get the first time someone signed up and then take a cumulative sum of this value:

select
  t.date,
  sum(
    sum(
      case
        when seqnum = 1 then 1
        else 0
      end
    )
  ) over (
    order by
      date
  ) as numUniques
from
  (
    select
      t.*,
      row_number() over (
        partition by user
        order by
          date
      ) as seqnum
    from
      t
  ) t
group by
  t.date
order by
  t.date

You can put a where clause in the subquery to restrict this to a particular set of dates.

Answer 2

Here is one way to do this.

if OBJECT_ID('tempdb..#Something') is not null
    drop table #Something

create table #Something
(
    MyDate date
    , UserID varchar(10)
)

insert #Something (MyDate, UserID) values
('01/01/2016', 'jacby'),
('01/01/2016', 'donta'),
('01/01/2016', 'stapf'),
('02/02/2016', 'kamed'),
('02/02/2016', 'jacby'),
('02/02/2016', 'carpi'),
('03/03/2016', 'slwig'),
('03/03/2016', 'kamed')

select distinct s.MyDate
    , 
    (
        select count(distinct UserID)
        from #Something s2
        where s2.MyDate <= s.MyDate
    ) as UniqueUsers
from #Something s