CODEWITHSUNDEEP
mysql
Dmitry
Dmitry

Reputation: 505

MySQL query - get users count of users who have placed FIRST order in current time period

I have two MySQL tables for users and their orders, with this structure (simplified for question):

[ USERS table ]

userid

[ ORDERS table ]

id

user_id

date

I need to make request to get COUNT of users who placed their FIRST order (First invoiced users count) in current period, let say for example in current month of current year.

I use request like this, let's say for "Apr 2019" for this example:

    SELECT   count(distinct orders.user_id) AS value
    FROM     orders,
             users
    WHERE    users.userid = orders.user_id
    &&       DATE_FORMAT(orders.date,"%b %Y") = "Apr 2019"
    &&       (
              SELECT DATE_FORMAT(orders.date,"%b %Y") AS firstorderdate 
              FROM orders 
              WHERE orders.user_id = users.userid 
              ORDER BY date ASC LIMIT 1) = "Apr 2019"
             )

What I'am trying to do at last part of query is get first ever user order and check that it's date located inside current period (Apr 2019). As I see this part of request is not correct, because we are checking ALL users and I don't know how to check CURRENT user in overall request for his first order.

Please help with correct MySQL query for getting first time invoiced users count in current period (for ex. month).

Upvotes: 0

Views: 1474

Answers (1)

TSungur
TSungur

Reputation: 406

If I understand you correctly; you first need to get the first orders ever made for every customer. Than you need to check if their first orders date is in your desired range and get a count of the customers matching your criteria.

So here is how it looks like:

select count(orders.user_id) from orders
inner join 
(
    select user_id, min(id) as firstOrderID from orders
    group by user_id
    ) as firstOrders

on orders.id=firstOrders.firstOrderID
where orders.date_ between '2019-04-01' and '2019-04-30'

You can check it in this fiddle

For the second part of your question in comments; You should filter in the inner query like this:

select count(orders.user_id) from orders
inner join 
(
    select user_id, min(id) as firstOrderID from orders
  inner join users on users.user_id=orders.user_id
  where users.user_type <> "ihs"
    group by user_id
    ) as firstOrders

on orders.id=firstOrders.firstOrderID
where orders.date_ between '2019-04-01' and '2019-04-30'

Upvotes: 3

Related Questions