mySQL – How can I see which users submitted orders last month but not this month?

I need to figure out the users who submitted orders last month but not this month. This is my current query to count total users, but how can I cross check to see who has bought this month but not the last?

SELECT DISTINCT(customer_user_id)
FROM customer_orders 
WHERE created_on > '2020-07-01'

This is my the customer orders table this would be the expected output, a list of all the users

EDIT: Additionally, I’m trying to get a report of the users who submitted orders in July but not August, with their amount total for the month, number of orders, and user cellphone #. This is the query I’m using but its not giving me the result.

SELECT DISTINCT(co.customer_user_id), SUM(co.amount), COUNT(co.order_id), s.cellphone
FROM customer_orders co
JOIN subscribers s
ON co.customer_user_id = s.user_id
WHERE co.created_on > '2020-07-01'
AND month(co.created_on) <> month(curdate());

This is my subscriber table, and above is my customer orders table.

Answer

As I understood you want to filter the user’s which are again created in current month. So,

-- not sure if you really need distinct
SELECT DISTINCT(c.customer_user_id)
  FROM customer_orders  c
 WHERE c.created_on > '2020-07-01'
   AND month(c.created_on) <> month(curdate());

Advertisement

Answer