x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 501

# Calculating Average In MySql

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
matthewdacruz
• 4
• 3
1 Solution

Commented:
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

Commented:
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
``````
Without seeing your table and data difficult to test but it should be something like that
0

Author Commented:
Hi Ray
I would love to see what you do with Php.
I have attached the tables as requested.
d-tables.sql
0

Commented:
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;
``````
0

Author Commented:
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

Commented:
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 Commented:
Thanks, That is what I was looking for.
0

Commented:
You are welcome - thanks for the points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.