title: New event count on each day
body: <p>I have a table Events which has 2 columns <code>dt</code> (Date) and <code>event</code>. I am trying to get the new event count for each day. For e.g. if the event appeared today and tomorrow, then it should only be counted for today (as it is show for the first time) and should be excluded from tomorrow's count.</p>
  
  <pre><code>+------------+-------+
  | dt         | event |
  +------------+-------+
  | 2019-02-01 | u1    |
  | 2019-02-01 | u2    |
  | 2019-02-01 | u3    |
  | 2019-02-01 | u4    |
  | 2019-02-01 | u1    |
  | 2019-02-02 | u5    |
  | 2019-02-02 | u1    |
  | 2019-02-02 | u6    |
  | 2019-02-02 | u4    |
  | 2019-02-03 | u8    |
  | 2019-02-03 | u9    |
  | 2019-02-03 | u6    |
  +------------+-------+
  </code></pre>
  
  <p>I'm trying to get the below output:</p>
  
  <pre><code>+------------+-------------------------+
  | dt         | count(distinct c.event) |
  +------------+-------------------------+
  | 2019-02-01 |                       4 |
  | 2019-02-02 |                       2 |
  | 2019-02-03 |                       2 |
  +------------+-------------------------+
  </code></pre>
  
  <p>The query I am using is:</p>
  
  <pre><code>with cte (dt,event,rnk) as
  (
  select dt, 
  event,
  row_number() over (partition by dt order by event) as rnk
  from events order by event
  )
  select e.dt, count(distinct c.event) from events e left join cte c
  on  e.event &lt;&gt; c.event where e.dt = c.dt
  group by 1;
  </code></pre>
  
  <p>But I am not able to filter out the already appeared event from the previous day. I believe something small is missing. The result from my query is: </p>
  
  <pre><code>+------------+-------------------------+
  | dt         | count(distinct c.event) |
  +------------+-------------------------+
  | 2019-02-01 |                       4 |
  | 2019-02-02 |                       4 |
  | 2019-02-03 |                       3 |
  +------------+-------------------------+
  </code></pre>
  
  <p>I am not sure if LEAD / LAG function can help solve the issue.
  Also is there a way that I could achieve this without using any joins.</p>
  
  <p>Table creation and insertion queries:</p>
  
  <pre><code>create table events (dt Date, event varchar(5));
  
  insert into events values('2019-02-01', 'u1');
  insert into events values('2019-02-01',  'u2');
  insert into events values('2019-02-01',  'u3');
  insert into events values('2019-02-01',  'u4');
  insert into events values('2019-02-01',  'u1');
  insert into events values('2019-02-02',  'u5');
  insert into events values('2019-02-02',  'u1');
  insert into events values('2019-02-02',  'u6');
  insert into events values('2019-02-02',  'u4');
  insert into events values('2019-02-03',  'u8');
  insert into events values('2019-02-03',  'u9');
  insert into events values('2019-02-03',  'u6');
  </code></pre>