title: SQL sum of column value, unique per user per day
body: <p>I have a postgres table that looks like this:</p>
  
  <pre><code>id | user_id | state | created_at
  </code></pre>
  
  <p>The state can be any of the following:</p>
  
  <pre><code>new, paying, paid, completing, complete, payment_failed, completion_failed
  </code></pre>
  
  <p>I need a statement that returns a report with the following:</p>
  
  <ol>
  <li>sum of all paid states by date</li>
  <li>sum of all completed states by date</li>
  <li>sum of all new, paying, completing states by date with only one per user per day to be counted</li>
  <li>sum of all payment_failed, completion_failed by date with only one per user per day to be counted</li>
  </ol>
  
  <p>So far I have this:</p>
  
  <pre><code>SELECT
    DATE(created_at) AS date,
    SUM(CASE WHEN state = 'complete' THEN 1 ELSE 0 END) AS complete,
    SUM(CASE WHEN state = 'paid' THEN 1 ELSE 0 END) AS paid
  FROM orders
  WHERE created_at BETWEEN ? AND ?
  GROUP BY DATE(created_at)
  </code></pre>
  
  <p>A sum of the in progress and failed states is easy enough by adding this to the select:</p>
  
  <pre><code>SUM(CASE WHEN state IN('new','paying','completing') THEN 1 ELSE 0 END) AS in_progress,
  SUM(CASE WHEN state IN('payment_failed','completion_failed') THEN 1 ELSE 0 END) AS failed 
  </code></pre>
  
  <p>But i'm having trouble figuring out how to make only one per user_id per day in_progress and failed states to be counted.</p>
  
  <p>The reason I need this is to manipulate the failure rate in our stats, as many users who trigger a failure or incomplete order go on to trigger more which inflates our failure rate.</p>
  
  <p>Thanking you in advance.</p>