title: Cumulative distinct count
body: <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>