title: MySQL Get cumulative customer transactions (not SQL transactions) on a monthly basis
body: <p>:)</p>
  
  <p>I have a simple table with transactions in it, nothing fancy, and I want to get for each month how many transactions are "first transactions". By first transaction we mean that the customer bought then for the first time.</p>
  
  <p>The result I am trying to get is in the following form:</p>
  
  <pre><code>+---------+----------------------------+------------------+
  |   Date  |  First Purchases In Month  |  Cumulative Sum  |
  +---------+----------------------------+------------------+
  | 01/2015 |           10               |       10         |
  +---------+----------------------------+------------------+
  | 02/2015 |           40               |       50         |
  +---------+----------------------------+------------------+
  </code></pre>
  
  <p>The date format doesn't really matter, it could be a full date, I will parse it in Java later on anyway.</p>
  
  <p>I found this post that does something similar although on a daily basis </p>
  
  <p><a href="https://stackoverflow.com/questions/26093264/how-can-i-get-count-of-customers-per-day-by-unique-and-repeat-customer-for-speci">How can i get count of customers per day by unique and repeat customer for specific date?</a></p>
  
  <p>and I turned it into this:</p>
  
  <pre><code>set @csum := 0;
  SELECT
      FirstOrderDate, MonthTotal, @csum:= @csum + MonthTotal AS AccumulatedTotal
  FROM
  (
      SELECT FirstOrderDate, SUM(month_total_new) as MonthTotal
      FROM
      (
          SELECT
              FirstOrderDate, COUNT(q1.consumerId) AS month_total_new
          FROM
          (
              SELECT
                  consumerId, MIN(date) AS FirstOrderDate
              FROM
                  transaction
              GROUP BY
                  consumerId
          ) AS q1
          GROUP BY
              EXTRACT(YEAR FROM q1.FirstOrderDate),
              EXTRACT(MONTH FROM q1.FirstOrderDate)
      ) AS s
      GROUP BY
          EXTRACT(YEAR FROM s.FirstOrderDate),
          EXTRACT(MONTH FROM s.FirstOrderDate)
  ) AS test;
  </code></pre>
  
  <p>You can see it in action here: <a href="http://sqlfiddle.com/#!9/31538/7" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/31538/7</a></p>
  
  <p>The results seem to be correct, but using so many inner queries doesn't seem to be a best practice approach. What would be the proposed way to do such a thing?</p>
  
  <p>Also, I would like to get 0 when a month has no "first transactions" in it rather than that date not appearing at all (like March in our fiddle where it has only one transaction which is not a "first transaction").</p>
  
  <p>Thank you very much for any help in advance! :)</p>