CODEWITHSUNDEEP
sqlamazon-athena
datazang
datazang

Reputation: 1169

What does AVG(x) - 1 give us?

I've been studying for the query that my colleague coded it. For each product, we counted the prices in a specific time period to see how the price has changed for each product. Let's say that this is "number_of_price".

And then, my colleague took the average of "number_of_price", and then he formulated it like this:

AVG(number_of_price) - 1 AS mean_price_updates

But to be frank, I have no clue why this equation gives us the mean_price_updates.

Any idea?

Upvotes: 1

Views: 52

Answers (2)

Turo
Turo

Reputation: 4914

If you have 3 different prices in a time period, you have 2 updates, i think it's as simple as that.

This and the fact that AVG(number_of_price - 1) = AVG(number_of_price) - 1

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

I suppose that the price change counts include the original price. E.g.:

+------+------------+-------+
| item | date       | price |
+------+------------+-------+
|    1 | 2020-01-01 | 10.00 |
|    1 | 2020-01-05 | 12.00 |
|    1 | 2020-01-16 | 13.00 |
|    2 | 2020-01-01 | 20.00 |
|    2 | 2020-01-27 | 23.00 |
+------+------------+-------+

That is three prices for product #1 and two prices for product #2. The avarage is 2.5 prices per product. Minus 1 is 1.5 price changes per product (two for #1 and 1 for #2).

Upvotes: 2

Related Questions