Join table to itself to find products bought one after the other

Question

I have a table which contains the following columns:

+------------------+----------+
| date_of_purchase | product  |
+------------------+----------+
| 2013-06-18       | A        |
| 2013-07-18       | A        |
| 2013-08-24       | A        |
| 2013-10-21       | A        |
| 2013-11-20       | A        |
| 2013-12-20       | A        |
| 2014-01-20       | A        |
| 2014-03-24       | A        |
| 2014-03-24       | B        |
| 2014-04-23       | B        |
| 2014-04-23       | A        |
| 2014-05-16       | B        |
| 2014-05-23       | A        |
+------------------+----------+

And I want to get something like this:

+----------+----------+------------+------------+
| product1 | product2 | date_a     | date_b     |
+----------+----------+------------+------------+
| A        | B        | 2014-01-20 | 2014-03-24 |
| A        | B        | 2014-03-24 | 2014-04-23 |
| B        | A        | 2014-05-16 | 2014-05-23 |
| A        | B        | 2014-04-23 | 2014-05-16 |
+----------+----------+------------+------------+

What I am trying to do is to check which product "B" is bought after product "A". I am new to stack-overflow, so the way I am asking this question might seem vague. But need help on this one.

Thanks Abhik. Was trying to figure out a way to put table values. @ AbhikChakraborty — g_yogini, Apr 05 '15 at 14:56
What do you mean "bought after"? Immediately in the next purchase? At any time afterwards? I can't figure out the logic behind your expected results. — Gordon Linoff, Apr 05 '15 at 15:00
I have simplified the content of table a bit. I am trying to analyse what products are bought in a sequence, not on the same day, but in the immediate next purchase. Eg.: Product A was bought several times, but the result should show only one row for product A followed by product B. — g_yogini, Apr 05 '15 at 15:11
The order of the rows in your first table is unclear. What determines that `2014-03-24,A` comes before `2014-03-24,B` and that `2014-04-23,B` comes before `2014-04-23,A`? Is there another column you use to order the results as you have shown? — FuzzyTree, Apr 05 '15 at 15:38
No. Just ordered it by date. The dates in the results are not so important. What is important is 4 rows in the result. — g_yogini, Apr 05 '15 at 15:42
Subqueries: http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql and auto_increment: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html — bf2020, Apr 05 '15 at 15:43
@bf2020, not sure what you are trying to suggest. Could you please explain? Thanks. :) — g_yogini, Apr 05 '15 at 15:49

score 0 · Accepted Answer · answered Apr 05 '15 at 16:06

The derived table keeps track of the next purchase date using a variable which is then used to check whether a different product was purchased on the next purchase date

select t1.product, t2.product, t1.date_of_purchase, t2.date_of_purchase from (
    select date_of_purchase, product, 
    @nextDateOfPurchase nextDateOfPurchase,
    @nextDateOfPurchase := date_of_purchase
    from Table1 t
    order by date_of_purchase desc
) t1 join Table1 t2 on t2.date_of_purchase = t1.nextDateOfPurchase
    and t2.product <> t1.product
    and t2.date_of_purchase <> t1.date_of_purchase
order by t1.date_of_purchase

http://sqlfiddle.com/#!9/44b24/3

Join table to itself to find products bought one after the other

1 Answers1