Sql: average of a dates

Question

I have to write a query to calculate the average number days between the shopping 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’);

should return that John Smith waited 1 day then 5 days, so his average is 3 days. Alice Cooper(!) waited 7 days so her average is 7. Bob Baker is a daily runner so his average is 1.

I have done something like this

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

but it doesn't work.

Any help will be greatly appreciated.

Answer 1

You have to convert your timestamp in epoch seconds to use avg aggregate 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;