mysql - SQL Repeated entries by time -


i have database tracking date transaction takes place, in addition unique buyer transaction corresponds - trying @ reservations resulted in customer purchasing again @ later date. right now, code , sample output below shows me customers repeat customers count of buyer_id, want able to see purchases (reservations) resulted in same customer purchasing again @ later time (not earlier in time, case using simple "count").

select r.id reservation_id, r.created, r.buyer_id, count(r.buyer_id) reservations r group r.buyer_id order reservation_id    reservation_id   created             buyer_id     count(r.buyer_id)  3                2007-08-14 18:28:38   438           1  7                2007-09-19 12:29:52   474           2  8                2007-09-19 13:14:54   476           1  9                2007-09-20 10:22:52   477           1  10               2007-09-25 15:27:45   485           3  11               2007-09-26 20:56:25   474           2  12 .... etc 

the goal able pull additional data each reservation , see factors of service have effect on customer coming repeat purchase. in case above, buyer #474 purchased twice, want able distinguish first purchase (when he/she did indeed come purchase again, 2nd , final purchase) second purchase (after no other purchases made buyer #474). in case, goal have output row shows:

reservation_id   created             buyer_id    count(r.buyer_id) returning  3                2007-08-14 18:28:38   438           1              0  7                2007-09-19 12:29:52   474           2              1  8                2007-09-19 13:14:54   476           1              0  9                2007-09-20 10:22:52   477           1              0  10               2007-09-25 15:27:45   485           3              1  11               2007-09-26 20:56:25   474           2              0  12 .... etc 

i.e., showing how customer 474's id not show again after reservations_id 11. in excel have huge amount of rows , excel can't handle functions on such large dataset.

any or suggestions appreciated.

this long comment.

what trying called recurrent event analysis. in particular, part of branch of statistics/data mining called "survival analysis".

your approach (which possible in sql) leads biased results , biased conclusions, simple reason first purchase long ago has greater chance of returning first purchase yesterday.

my response questions never read 1 of books. however, book "data analysis using sql , excel" has 2 chapters on survival analysis. these can understand such time-to-event problems, practical perspective.


Comments

Popular posts from this blog

blackberry 10 - how to add multiple markers on the google map just by url? -

php - guestbook returning database data to flash -

delphi - Dynamic file type icon -