title: SQL: Count first instance of ids on each date listed in table
body: <p>New to SQL.  </p>
  
  <p>Given a table called "badge" with the following fields:</p>
  
  <pre><code>badge.id, badge.createdAt, badge.visitorId
  </code></pre>
  
  <p>and a table "visitor" with the following fields:</p>
  
  <pre><code>visitor.id
  
  where badge.visitorId = visitor.id
  </code></pre>
  
  <p>Obviously I'm leaving out some fields for simplicity, here, but how can I get back a list of dates that shows a count of only the first instance of visitorId on that day? I tried:</p>
  
  <pre><code>SELECT COUNT(DISTINCT(badge."visitorId")), 
         date_trunc('day', badge."createdAt") AS day 
  FROM badge 
  GROUP BY day 
  ORDER BY "day";
  </code></pre>
  
  <p>but that's not filtering out the duplicates.  The desired output would look something like this:</p>
  
  <pre><code>count  day
  1671   2016-03-13 00:00:00
  1646   2016-03-14 00:00:00
  1050   2016-03-15 00:00:00
  </code></pre>
  
  <p>Where the count reflects the removed duplicates.  (The table above is the current count and I have confirmed there are several hundred duplicates).</p>