Solved

Calculating Average In MySql

Posted on 2013-06-26
8
470 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
  • 4
  • 3
8 Comments
 
LVL 108

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 51

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
 
LVL 51

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 51

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 51

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now