1

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.

I have two tables

  1. User table including timestamp for when user registered and user ID
  2. Purchase table including user IDs (seen in user table)

What I need: Table showing cumulative new users (registered in current month) who have made a purchase in the current month.

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.

One part of the question has been answered here in my opinion - Cumulative distinct count

But how to check against registration date is not coming to me. Any help is appreciated.

User Table Columns ID, Email, Timestamp (timestamp records date of onboarding)

Purchase Table Columns ID, User ID, User Email, Product ID, Timestamp (timestamp here is time of purchase)

Edit 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.

Here's my current 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
Osoba Osaze
  • 93
  • 1
  • 17
  • `WHERE YEAR(registrationdate) = YEAR(NOW()) AND MONTH(registrationdate) = MONTH(NOW())` – Barmar Feb 26 '18 at 21:52
  • Please provide any query you have attempted for this. We don't even know the table or column names, at least by looking at an existing query we can fill out the wanted logic. And/or you could provide sample data for each table. – Paul Maxwell Feb 26 '18 at 21:54
  • Added the base code for how I currently get the total purchases on a daily basis. I need to now count distinct user IDs and also check for new users registered in the current month – Osoba Osaze Feb 27 '18 at 13:39

2 Answers2

1

Not knowing the full structure of your tables, I'll give it my best shot.

SELECT u.*                    -- This will give you all info on all users
FROM users u
INNER JOIN purchases p
    ON p.userID = u.ID
WHERE u.registrationDate BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

I think this'll work, given the limited info available. The time interval bit might be a bit different but likely not.

Matt C
  • 21
  • 4
0

How about

SELECT DISTINCT u.id, u.whatever
FROM user_table u INNER JOIN purchase_table p
ON (u.id = p.userid) 
WHERE u.signup >= DATE_FORMAT(NOW() ,'%Y-%m-01')

This should get you the current calendar month's signups who have made a purchase.

Paul Campbell
  • 1,906
  • 2
  • 12
  • 19