user3605720 - 1 year ago 81

SQL Question

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!

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.

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 Source

```
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
```

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
```