user3605720 - 6 months ago 36
SQL Question

# Calculate Growth Rate Using PHP and Mysql

I'm trying to calculate the compound annual growth rate (CAGR) of a few products for various time periods. I have a MySQL database that looks like this:

``````product, asof, sales
abc, 2013-06-30, 36000000
abc, 2013-12-31, 48000000
abc, 2014-01-31, 51000000
abc, 2014-02-28, 56000000
xyz, 2013-06-30, 26000000
xyz, 2013-12-31, 33000000
xyz, 2014-01-31, 33000000
xyz, 2014-02-28, 36000000
``````

I've figured out the function of CAGR should look like this:

``````// EV = Ending Value
// BV = Beginning Value

function cagr(\$EV, \$BV, \$Period ) {
\$cagr = pow((\$EV/\$BV),(1/\$Period)) - 1;
return \$cagr;
}
``````

Any help or direction would be much appreciated! Thanks!

EDIT :

My end goal is to create a table that contains a 3 Month, 1 Year, and 3 Year compound annual growth rate for each product. So more specifically I need help on what MySQL queries are needed and how to structure the PHP in order to populate the table.

EDIT 2 :

So I've got it figured out how to get the CAGR's based on @Barmar help. What I can't figure out is how to use PHP to get this into a table format.

Is the best way to get month end dates for various time periods using strtotime like below?

``````\$lastdate = "2014-04-30";
\$month3 = date('Y-m-d', strtotime('last day 3 months ago', strtotime(\$lastdate)));
\$month6 = date('Y-m-d', strtotime('last day 6 months ago', strtotime(\$lastdate)));
\$year1 = date('Y-m-d', strtotime('last day of months 1 year ago', strtotime(\$lastdate)));
\$year3 = date('Y-m-d', strtotime('last day 36 months ago', strtotime(\$lastdate)));
\$year5 = date('Y-m-d', strtotime('last day 60 months ago', strtotime(\$lastdate)));
``````

I've been using a function to pass in the dates into the MySQL query that @Barmar described below.

``````function queryproduct (\$db, \$end_date, \$begin_date) {

\$q_product = "SELECT r.product, POWER(s1.sales/s2.sales, 1/(datediff(end_date, begin_date)/365)) - 1 AS cagr
FROM (
SELECT product, MAX(asof) AS end_date, MIN(asof) AS begin_date
FROM test
WHERE asof BETWEEN '\$begin_date' AND '\$end_date'
GROUP BY product
-- Prevent divide by 0 if we don't have a range
HAVING end_date != begin_date) AS r
JOIN test AS s1 ON s1.product = r.product AND s1.asof = end_date
JOIN test AS s2 ON s2.product = r.product AND s2.asof = begin_date;";

\$q_result = mysqli_query(\$db, \$q_product);

return \$q_result;
}
``````

Answer
``````SELECT r.product, POWER(s1.sales/s2.sales, 1/(datediff(end_date, begin_date)/365)) - 1 AS cagr
FROM (
SELECT product, MAX(asof) AS end_date, MIN(asof) AS begin_date
FROM sales
WHERE asof BETWEEN :range_start AND :range_end
GROUP BY product
-- Prevent divide by 0 if we don't have a range
HAVING end_date != begin_date) AS r
JOIN sales AS s1 ON s1.product = r.product AND s1.asof = end_date
JOIN sales AS s2 ON s2.product = r.product AND s2.asof = begin_date
``````

DEMO

UPDATE:

You can get multiple periods with:

``````SELECT DISTINCT r1y.product product, end_date,
begin1y, POWER(s_end.sales/s_1y.sales, 1/(datediff(r1y.end_date, begin1y)/365)) - 1 AS cagr_1y,
begin3y, POWER(s_end.sales/s_3y.sales, 1/(datediff(r1y.end_date, begin3y)/365)) - 1 AS cagr_3y,
begin5y, POWER(s_end.sales/s_5y.sales, 1/(datediff(r1y.end_date, begin5y)/365)) - 1 AS cagr_5y
FROM (
SELECT product, MAX(asof) AS end_date, MIN(asof) AS begin1y
FROM sales
WHERE asof > DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY product
-- Prevent divide by 0 if we don't have a range
HAVING end_date != begin1y) AS r1y
JOIN (
SELECT product, MIN(asof) AS begin3y
FROM sales
WHERE asof > DATE_SUB(NOW(), INTERVAL 3 YEAR)
GROUP BY product) AS r3y
ON r1y.product = r3y.product
JOIN (
SELECT product, MIN(asof) AS begin5y
FROM sales
WHERE asof > DATE_SUB(NOW(), INTERVAL 5 YEAR)
GROUP BY product) AS r5y
ON r1y.product = r5y.product
JOIN sales AS s_end ON s_end.product = r1y.product AND s_end.asof = r1y.end_date
JOIN sales AS s_1y ON s_1y.product = r1y.product AND s_1y.asof = begin1y
JOIN sales AS s_3y ON s_3y.product = r1y.product AND s_3y.asof = begin3y
JOIN sales AS s_5y ON s_5y.product = r1y.product AND s_5y.asof = begin5y
``````

DEMO

Source (Stackoverflow)
Comments