Cumulative distinct count
<p>I am working on query to get cumulative distinct count of uids on daily basis. </p>

<p>Example : Say there are 2 uids (100,200) appeared on  date 2016-11-01 and they also appeared on next day with new uid 300 (100,200,300) on 2016-11-02 
At this point i want store cumulative count to be 3 not 5 as (user id 100 and 200 already appeared on past day ).</p>

<pre><code>Input table:

    date            uid         
2016-11-01          100
2016-11-01          200
2016-11-01          300
2016-11-01          400         
2016-11-02          100
2016-11-02          200                 
2016-11-03          300
2016-11-03          400
2016-11-03          500
2016-11-03          600
2016-11-04          700

Expected query result:

date            daily_cumulative_count
2016-11-01              4   
2016-11-02              4
2016-11-03              6
2016-11-04              7
</code></pre>

<p>Till now i am able to get cumulative distinct count per day but it includes previous distinct uids from previous day as well.</p>

<pre><code>SELECT 
  date, 
  SUM(count) OVER (
    ORDER BY date ASC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )
FROM (
  SELECT 
    date, 
    COUNT(DISTINCT uid) AS count
  FROM sample_table
  GROUP by 1
)ORDER BY date DESC;
</code></pre>

<p>Any kind of help would be greatly appreciated. </p>