Solved

Calculating Average In MySql

Posted on 2013-06-26
8
476 Views
Last Modified: 2013-06-26
Hi Experts
I have a query written in MySQL. Not sure if I am doing this correctly.
I want to achieve the following:

I need to find out the the average amount of months that a group of users use a product for.
See 'AS MEMBERSHIP_LTV' in query.

I have attempted to use datediff for this but am not sure if this is correct. Im not sure if I am going at this in the totally incorrect way and need some help please.

How does one calculate what the average subscription time in months per product across multiple users?
How long does the product user group stay subscribed for on average?

The columns that manage the state date and end date are:

 - dap_users_products_jn.access_start_date
 - dap_users_products_jn.access_end_date

How doe we use these multiple dates to work out a group average because the indiviuals subscribe at different dates

I am wanting to know grouped by
dap_products.id,

I want to look at these numbers  by year to establish if the changes we make to our products we offer are improving the time the customers stay subscribed for looking back over the years.

Any suggestions or advice will be appreciated

Thanks

Matt
Member-LTV-by-product.sql
0
Comment
Question by:matthewdacruz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39278153
While you can probably do this in a MySQL query, if you're using PHP it might be easier to collect the data into arrays of objects and run the analysis over the arrays.  Each object would have the data elements you want to aggregate and analyze.  You can use PHP usort() to order and reorder the arrays.

If you want to post the CREATE TABLE statement for dap_products and dap_users_products_jn we might be able to help with some ideas.
0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 39278707
So you want to know for a given group of users U what the average time a user uses your product where the time used is the difference between

dap_users_products_jn.access_start_date
AND
dap_users_products_jn.access_end_date

How do you want partial months to be treated - round up / down or sum as is?

something probably like this
SELECT 
  AVG(DATEDIFF(dap_users_products_jn.access_end_date, dap_users_products_jn.access_start_date))/29.5, 
  dap_products.id 
FROM dap_products
WHERE
  dap_products.is_recurring ='y' AND 
  dap_users_products_jn.access_end_date < '2020-01-01'
GROUP BY 
  YEAR(dap_users_products_jn.access_end_date), 
  dap_products.id

Open in new window

Without seeing your table and data difficult to test but it should be something like that
0
 

Author Comment

by:matthewdacruz
ID: 39278763
Hi Ray
I would love to see what you do with Php.
I have attached the tables as requested.
d-tables.sql
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 57

Expert Comment

by:Julian Hansen
ID: 39279094
This should be solvable in MySQL without having to resort to PHP code - did you try the query I posted

Here is an updated version tested against your tables

SELECT 
  AVG(DATEDIFF(dup.access_end_date, dup.access_start_date))/29.5, 
  dp.id 
FROM dap_users_products_jn dup LEFT JOIN dap_products dp ON dup.product_id = dp.id
WHERE
  dp.is_recurring ='y' AND 
  dup.access_end_date < '2020-01-01'
GROUP BY 
  YEAR(dup.access_end_date), 
  dp.id;

Open in new window

0
 

Author Comment

by:matthewdacruz
ID: 39279135
Hi Julian

Thanks for the response, I have run the query.
i have a question. Does your query give the average between all the dates per product?
Does it look through all the dates associated with one product and then average it out?

This information is crucial so I don't want to get it wrong.
I attached the db tables in the previous post for the PHP request.

Thanks

Matt
0
 
LVL 57

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 39279166
Yes the query is producing an average of days groupd by product ID. The average is divided by 29.5 to give months.

So the query will group all users with like product id's together.

Subtract the start date from the end date for all users

Average these by product ID

If I read your question correctly - this is what you are looking for?
0
 

Author Closing Comment

by:matthewdacruz
ID: 39279242
Thanks, That is what I was looking for.
0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 39280432
You are welcome - thanks for the points
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Testrail - Active Directory integration. 4 35
e commerce steps shown instead of self testing 2 58
Secure registration PHP 7 43
sitemap.xml 3 32
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question