Solved

Calculating Average In MySql

Posted on 2013-06-26
8
480 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 58

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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
LVL 58

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 58

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 58

Expert Comment

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to dynamically set the form action using jQuery.
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.

621 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