FERNANDO ORTIZ FERNANDO ORTIZ - 1 month ago 6
MySQL Question

Customers who bought only this month and not before sql

I have a list of records of purchases and need to know the total users who bought in the current month, but have never bought in past months
date_buy = date of purchase

$hoy = date("Y-m-d");
$mesActual = date("Y-m-01");

SELECT count(DISTINCT(id_user))
FROM transaction
where date_buy BETWEEN '$mesActual' and '$hoy'
AND date_buy >(select min(date_buy) FROM transaction)


thanks

Answer

Possible way is

 SELECT count(DISTINCT(id_user)) 
 FROM transaction t1
 WHERE date_buy BETWEEN '$mesActual' and '$hoy'
    AND NOT EXISTS (SELECT 1 FROM transaction t2 WHERE t2.id_user = t1.id_user AND t2.date_buy < '$mesActual')
Comments