Hi all,
I have a problem to calculate the average length of buying any product for each customer. The way that I need to calculate this is to find the length based on payments for consecutive months.
For example,
I have a table called tbl.transactions with the following fields. For a customer, I have this:
Customer_Id, payment_date, payment_amount, product_type
10000001, 15/01/2016, $40, jean
10000001, 03/02/2016, $20, shoes
10000001, 23/03/2016, $30, t-shirt
10000001, 13/04/2016, $40, jean
10000001, 03/07/2016, $20, shirt
10000001, 03/08/2016, $60, jacket
10000001, 05/10/2016, $50, jean
10000001, 08/11/2016, $60, shoes
10000001, 27/12/2016, $20, jean
What I want to achieve is to find the difference of the months (not dates) based on consecutive payments first regardless of the product sold. So, I'd like to see
Customer_Id, initial_payment_date, difference_in_months
10000001, 15/01/2016, 4
10000001, 03/07/2016, 2
10000001, 05/10/2016, 3
*imagine that there are other customers with other customerids but this is a simplified example so that I can see how to achieve this.
And then I simply need to get an average for that customer. If I get the previous table somehow, then I can do the rest easily and achieve this:
Customer_Id, average_buying_in_months
10000001, 3
I put this last result just in case you think there is a better way for me to achieve this without the previous table.
Any thoughts how to do this? What type of query should I do? Anyone who has done something like this before?
Thanks
I have a problem to calculate the average length of buying any product for each customer. The way that I need to calculate this is to find the length based on payments for consecutive months.
For example,
I have a table called tbl.transactions with the following fields. For a customer, I have this:
Customer_Id, payment_date, payment_amount, product_type
10000001, 15/01/2016, $40, jean
10000001, 03/02/2016, $20, shoes
10000001, 23/03/2016, $30, t-shirt
10000001, 13/04/2016, $40, jean
10000001, 03/07/2016, $20, shirt
10000001, 03/08/2016, $60, jacket
10000001, 05/10/2016, $50, jean
10000001, 08/11/2016, $60, shoes
10000001, 27/12/2016, $20, jean
What I want to achieve is to find the difference of the months (not dates) based on consecutive payments first regardless of the product sold. So, I'd like to see
Customer_Id, initial_payment_date, difference_in_months
10000001, 15/01/2016, 4
10000001, 03/07/2016, 2
10000001, 05/10/2016, 3
*imagine that there are other customers with other customerids but this is a simplified example so that I can see how to achieve this.
And then I simply need to get an average for that customer. If I get the previous table somehow, then I can do the rest easily and achieve this:
Customer_Id, average_buying_in_months
10000001, 3
I put this last result just in case you think there is a better way for me to achieve this without the previous table.
Any thoughts how to do this? What type of query should I do? Anyone who has done something like this before?
Thanks