fetching customers who re-subscribed on their own

Question

I have a payments table I reproduced on the following sqlfiddle : http://sqlfiddle.com/#!9/8d31c9

I'm trying to extract the following data :

• fetch the customers who RE-subscribed on their own (that means "type" equals "SUBSCRIPTION" and "advisor_create" equals 0) in 2015. To be considered as a RE-subscriber, the customer should have at least one previous payment with type "SUBSCRIPTION" (year and advisor_create are not important for this previous payment)
• I'll have more data to extract once this base query is working, like knowing if these customers re-subscribed for a different offer or the same one, if their new subscription is cheaper or more expensive than the previous one.

In the sqlfiddle example data I provided, this query should return the customers #2 (due to payment id #4) and #5 (due to payment id #8).

Right now, I wrote the following query :

SELECT p.id, p.customer_id, p.date, p.type, p.offer, p.value
FROM payments p
WHERE p.type = "SUBSCRIPTION"
AND YEAR(p.date) = 2015
AND advisor_create = 0

But I have no idea how to self join this query with the payment table to check if subscriptions occured in the past for these customers. Any help would be appreciated.

UPDATE #1 :

According to the answer below, I tried to work with the second part of my problem and tried the following query :

SELECT p.customer_id, P.offer, p.value, previous_payments.offer AS previous_offer, previous_payments.value AS previous_value
FROM payments p 
JOIN payments previous_payments ON ( 
    previous_payments.customer_id = p.customer_id 
    AND previous_payments.date < p.date 
    AND previous_payments.type = 'SUBSCRIPTION' 
) 
WHERE p.type = 'SUBSCRIPTION' 
AND p.advisor_create = 0 
AND YEAR(p.date) = 2015
GROUP BY p.customer_id 

The data for customer #5 are wrong, previous_offer and previous value should equals 21 and 14, as this is the latest previous subscription for this customer. How can I make the join runs on the latest previous subscription ?

UPDATE #2

According to my comment pointing out an error in the final result set, I fixed your latest query (the one with temporary join table, see your EDIT 2 part) like this :

SELECT p.customer_id, p.offer, p.value, previous_payments.offer AS previous_offer, previous_payments.value AS previous_value
FROM payments p
JOIN (
    SELECT *
    FROM payments
    JOIN ( select customer_id, max(date) as date from payments p1
        where date < ( select max(date) from payments p2 where p1.customer_id = p2.customer_id and p2.advisor_create = 0 )
    group by customer_id ) p2 USING (customer_id, date)
    WHERE type = 'SUBSCRIPTION'
) previous_payments ON previous_payments.customer_id = p.customer_id AND previous_payments.date < p.date
WHERE p.type = 'SUBSCRIPTION'
AND p.advisor_create = 0
AND YEAR(p.date) = 2015;

Is that correct ? Just added and p2.advisor_create = 0 in the sub-sub-sub-query

Answer 1

how to self join this query with the payment table

Here is one way of doing it:

SELECT p.customer_id
FROM payments p
JOIN payments previous_payments ON (
    previous_payments.customer_id = p.customer_id 
    AND previous_payments.date < p.date 
    AND previous_payments.type = 'SUBSCRIPTION'
)
WHERE p.type = 'SUBSCRIPTION'
AND p.advisor_create = 0
AND YEAR(p.date) = 2015
GROUP BY p.customer_id;

There are plenty other options though.

EDIT

Ok so apparently this only answers half your problem. The reason you are not getting the right previous payments with the above query is because of the GROUP BY. So we need to remove it. Let's decompose the problem.

You need to know the previous payment. That is the second to last payment.

Here is how to get the date of the last payment of a given customer:

SELECT MAX(date) FROM payments WHERE customer_id = X;

Here is how to get the date of the second to last payment of a given customer:

SELECT MAX(date) FROM payments p1
WHERE p1.date < ( SELECT MAX(date) FROM payments p2 WHERE p1.customer_id = p2.customer_id )
AND p1.customer_id = X 

Now we can add this as a where clause to the original query in a correlated subquery, to only keep the last payment, and therefore remove the group by.

SELECT p.customer_id, p.offer, p.value, previous_payments.offer AS previous_offer, previous_payments.value AS previous_value
FROM payments p
JOIN payments previous_payments ON (
    previous_payments.customer_id = p.customer_id 
    AND previous_payments.date < p.date 
    AND previous_payments.type = 'SUBSCRIPTION'
)
WHERE p.type = 'SUBSCRIPTION'
AND p.advisor_create = 0
AND YEAR(p.date) = 2015
AND previous_payments.date = (
    SELECT MAX(date) FROM payments p1
    WHERE date < ( SELECT MAX(date) FROM payments p2 WHERE p1.customer_id = p2.customer_id )
    AND p1.customer_id = p.customer_id
);

Returns:

+-------------+-------+-------+----------------+----------------+
| customer_id | offer | value | previous_offer | previous_value |
+-------------+-------+-------+----------------+----------------+
|           2 |    23 | 11.00 |             21 |          12.00 |
|           5 |    21 | 15.00 |             21 |          14.00 |
+-------------+-------+-------+----------------+----------------+

EDIT 2

Here is another way to write the same query, without the correlated subquery, but using a temporary join table instead (so it might be faster):

SELECT p.customer_id, p.offer, p.value, previous_payments.offer AS previous_offer, previous_payments.value AS previous_value
FROM payments p
JOIN (
    SELECT *
    FROM payments
    JOIN ( select customer_id, max(date) as date from payments p1
        where date < ( select max(date) from payments p2 where p1.customer_id = p2.customer_id )
    group by customer_id ) p2 USING (customer_id, date)
    WHERE type = 'SUBSCRIPTION'
) previous_payments ON previous_payments.customer_id = p.customer_id AND previous_payments.date < p.date
WHERE p.type = 'SUBSCRIPTION'
AND p.advisor_create = 0
AND YEAR(p.date) = 2015;