Counting customers with special history in SQL

[pat1234]

hello
i've a CUSTOMERS table (customer_id) and a PURCHASES table (customer_id, purchase_date,purchase_id)
I'm trying to count the number of customers who bought purchase_id = X and, in the 12 months before buying X, also bought at least twice purchase_id = Z.
Any idea ?
Thanks in advance
Patrick

 

[PHV]

The syntax depends on your RDBMS:
SELECT COUNT(DISTINCT C.customer_id)
FROM CUSTOMERS C INNER JOIN PURCHASES P ON C.customer_id=P.customer_id
WHERE P.purchase_id=X AND 2<=(SELECT COUNT(*) FROM PURCHASES Q WHERE Q.customer_id=P.customer_id AND Q.purchase_id=Z AND Q.purchase_date BETWEEN P.purchase_date - 12 UNITS MONTH And P.purchase_date)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swampBoogie]

select count(*) 
from customers
where customer_id in 
  (select customer_id from purchases p
    where purchase_id = 'X'
    and 2 <= (select count(*)
   from purchases
    where customer_id = p.customer_id
      and purchase_id = 'Z'
      and purchase_date > p.purchase_date 
          - interval '12' month(2)))

 

[pat1234]

thanks to all it works fine.
May i ask you one more thing?
I 'd like to find in a single query the number of customer grouped by their last purchase_id before a certain date.
That is I want to have eg :
- 1200 cutomers whose last purchase_id='X'
- 800 cutomers whose last purchase_id='Y' and so on

I believe that i need to use GROUP BY and HAVING (and tested some stuff about it) but i can't make it work.
Thanks again if you have an idea.
PAtrick

 

[PHV]

Something like this ?
SELECT P.purchase_id, COUNT(*) Customers
FROM PURCHASES P INNER JOIN (
SELECT customer_id, MAX(purchase_date) LastDate FROM PURCHASES GROUP BY customer_id
) M ON P.customer_id=M.customer_id AND P.purchase_date=M.LastDate
GROUP BY P.purchase_id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pat1234]

no this does not work .
I have got a syntax error on FROM clause.