title: How to get cumulative total users but ignoring the users who already appear in previous day? using bigquery
body: <p>So I want to calculate cumulative users per day but if the users exist is previous days they will not counted.</p>
  <pre><code>date_key      user_id
  2022-01-01     001
  2022-01-01     002
  2022-01-02     001
  2022-01-02     003
  2022-01-03     002
  2022-01-03     003
  2022-01-04     002
  2022-01-04     004
  </code></pre>
  <p>on daily basis we can get</p>
  <pre><code>date_key     total_user
  2022-01-01      2
  2022-01-02      2
  2022-01-03      2
  2022-01-04      2
  </code></pre>
  <p>if we simply calculate cumulative we can get 2,4,6,8 for each day the goal is to get the table like this</p>
  <pre><code>date_key     total_user
  2022-01-01      2
  2022-01-02      3
  2022-01-03      3
  2022-01-04      4
  </code></pre>
  <p>im using this query to get the result, since the data is really2 huge. the query takes forever to complete.</p>
  <pre><code>select b.date_key,count(distinct a.user_id) total_user
  from t1 a
  join t1 b 
     on b.date_key &gt;= a.date_key 
     and date_trunc(a.date_key,month) = date_trunc(b.date_key,month)
  group by 1
  order by 1
  </code></pre>
  <p>and yes the calculation should be on reset when the month is changing.</p>
  <p>and btw I'm using google bigquery</p>