CODEWITHSUNDEEP
mysqlmetabase
Osoba Osaze
Osoba Osaze

Reputation: 93

Counting distinct new users in purchase table (MySQL)

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

Upvotes: 1

Views: 1472

Answers (2)

Paul Campbell
Paul Campbell

Reputation: 1986

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.

Upvotes: 0

Matt C
Matt C

Reputation: 21

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.

Upvotes: 1

Related Questions