0
$\begingroup$

The other day I was asked to calculate "Average purchases for the last 3 months" with a Oracle sql query. What I did was (in pseudocode)

select avg(amount) avgpurchase from table where date between last 3 months and today.

This is the average for only one customer, I simplified the task.

According to this the avg function does this:

The Oracle AVG function returns the average of a column of numbers. An average is obtained by adding all of the values in a set and then dividing by the number of values in the set.

This is exactly the definition of average in my mind. However, a coworker told me my calculations were wrong, the average values should be way higher; she told me she was calculating this way the average:

select avgpurchase / 3 from (
select sum(amount) avgpurchase from table where date between last 3 months and today) subquery

I don't think this is the correct way to do this. In this scenario we are talking about a single customer; a single customer does not make the same amount of purchases every month (i.e. in april he did 100 purchases, in may 97, and so on).

Is it correct to divide by 3? is there a way to algebraically demonstrate that her approach is not correct? I'm basing my solution on the sentence "Average purchases for the last 3 months"

$\endgroup$
0
$\begingroup$

Your solution finds the average price of the purchases during the last 3 months. Your coworkers solution finds the average purchases per each month.

Now, I dont understand your question. You want the average per month of a consumer? In that case you should have a group by Consumer_ID in the query of your coworker. If you want average of consumers you should use something like

select avg(avgpurchase) avg from (
select sum(amount) avgpurchase from table where date between last 3 months 
and today Group by Consumer_ID) subquery

About the /3 part it depends weather you want it for each month, or not. You should as whoever gave you the task. Language is ambiguous.

Your solution is wrong either way.

EDIT: If you have only one consumer, it doesnt make sense to have something other the coworkers solution. I guess you could find the average prices of the purchases(as you have done) but, i dont see how that could be useful. So i guess what is required is what the coworker has done.

$\endgroup$
2
  • $\begingroup$ I just simplified the query, in the table we have different customers, just to make it simple I'm talking like if exists just one customer, it does not affect the solution or what I'm looking to resolve $\endgroup$
    – Naty Bizz
    Jun 14 '17 at 15:47
  • $\begingroup$ ok, see the edited answer without the group By part. Ask if you have any questions. $\endgroup$
    – kkica
    Jun 14 '17 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.