Sql: average number of dates

Question

I need to write a query to calculate the average number of days between purchases for each customer (without using subqueries).

create table data { customer varchar(20) not null, bought date not null, primary key (customer,bought) } 

For example,

 insert into data (customer,bought) values ('John Smith', date '2011-02-01'), ('Alice Cooper', date '2011-02-01'), ('Bob Baker', date '2011-02-01'), ('John Smith', date '2011-02-02'), ('Bob Baker', date '2011-02-02'), ('Bob Baker', date '2011-02-03'), ('Bob Baker', date '2011-02-04'), ('Bob Baker', date '2011-02-05'), ('Bob Baker', date '2011-02-06'), ('Bob Baker', date '2011-02-07'), ('John Smith', date '2011-02-07'), ('Alice Cooper', date '2011-02-08'); 

must return so that John Smith waits 1 day, and then 5 days, so his average age is 3 days. Alice Cooper (!) Waited 7 days for her to be average - 7. Bob Baker is a daily runner, so his average is 1.

I did something like this

 select distinct customer, avg (bought) as average from data; 

but that will not work.

Any help would be greatly appreciated.

Answer 1

PostgreSQL version of related response

 select customer, (max(bought) - min(bought)) / (count(bought)-1) from data group by customer; 

Answer 2

You need to convert the timestamp in seconds to seconds to use the avg aggregation function:

 SELECT customer, timestamp without time zone '1970-01-01' + cast( avg(EXTRACT(EPOCH FROM bought::timestamp) )::text as interval) FROM data GROUP BY customer; 

Answer 3

You might want to use a group by expression

 select customer, datediff(D, min(bought), max(bought)) / count(bought) as average from data group by customer 

Whenever you have a summary function in the selection list, you should use grouping for other fields that are not members of the aggregation.

This has been tested on SQL Server, and the syntax may be different from Postgresql, which I do not use.

Answer 4

This worked for me in a similar situation:

 SELECT customer, avg(AGE(now(),bought)) as waited FROM data GROUP BY customer 

Answer 5

choosing max-min / count is NOT average. try:

  timestamp 'epoch' + avg(date_part(epoch, date_column)) * interval '1 second' 

Answer 6

 SELECT customer, AVG(bought) AS average FROM data GROUP BY customer