title: Counting distinct new users in purchase table (MySQL)
body: <p>I'm using metabase to generate client reports on multiple metrics. I have beginner knowledge in SQL but the GUI has helped in building most queries except a few.</p>
  
  <p>I have two tables</p>
  
  <ol>
  <li>User table including timestamp for when user registered and user ID</li>
  <li>Purchase table including user IDs (seen in user table)</li>
  </ol>
  
  <p>What I need: Table showing cumulative new users (registered in current month) who have made a purchase in the current month.</p>
  
  <p>I assume joining both tables and then counting distinct ids grouped by day while checking register date should solve this, but I have a very sketchy idea of what the actual query will look like.</p>
  
  <p>One part of the question has been answered here in my opinion - <a href="https://stackoverflow.com/questions/40515839/cumulative-distinct-count">Cumulative distinct count</a></p>
  
  <p>But how to check against registration date is not coming to me. Any help is appreciated.</p>
  
  <hr>
  
  <p>User Table Columns
  ID, Email, Timestamp (timestamp records date of onboarding)</p>
  
  <p>Purchase Table Columns
  ID, User ID, User Email, Product ID, Timestamp (timestamp here is time of purchase)</p>
  
  <p><strong>Edit</strong>
  Thanks for the comments so far, I've been able to get new user IDs on every day making a purchase in the current month. Now I need to a row with the cumulative sum of these IDs so at the end of the time period, I know how many new users were added.</p>
  
  <p>Here's my current code</p>
  
  <pre><code>SELECT count(DISTINCT p.`user_id`) Users_Activated, date(p.`timestamp`) Day
  FROM `purchase` p
  INNER JOIN `user` u ON u.`id` = p.`user_id`
  WHERE date(u.`timestamp`) BETWEEN {{date1}}
     AND {{date2}} AND date(p.`timestamp`) BETWEEN {{date3}} AND {{date4}}
  GROUP BY date(p.`timestamp`)
  ORDER BY date(p.`timestamp`) ASC
  </code></pre>