title: SQL: trying to get the number of transactions when it's the first transaction by a user (new revenue)
body: <p>I have a table called <code>subscription_transaction</code> that has a <code>subscription_id</code>, <code>user_id</code>, and <code>timestamp</code>.</p>
  <p>These are payment transactions, and I want to figure out my &quot;new revenue&quot; daily. That would be the number of transactions by a user who is making their first transaction.</p>
  <p>I have tried the following but I get no results.</p>
  <pre><code>SELECT Floor(Extract(epoch FROM t1.timestamp) / 86400) * 86400 AS &quot;time&quot;, 
         Count(t1.id) * 60 
  FROM   subscription_transaction t1 
  WHERE  t1.event = 'one-time payment' 
         AND t1.subscription_id = 28 
         AND EXISTS (SELECT * 
                     FROM   subscription_transaction t2 
                     WHERE  t2.user_id = t1.user_id 
                            AND t2.timestamp &lt; t1.timestamp 
                     LIMIT  1) 
  GROUP  BY 1 
  ORDER  BY 1 
  </code></pre>
  <p>And if I flip the <code>&lt;</code> sign in the nested <code>SELECT</code>, making it <code>t2.timestamp &gt; t1.timestamp</code>, I get <em>all</em> transactions.</p>
  <p>Can anybody help?
  Thanks!</p>